我在SQL for MS Access中有两个查询。第一个是:
SELECT AVG(Salary) AS AverageSalary FROM Table WHERE EducationLevel = 1;
第二个查询:
SELECT AVG(Salary) AS AverageSalary2 FROM Table WHERE EducationLevel = 2;
我如何将它们组合成一个查询来生成一个具有两列的表,标记为";平均工资;以及";平均工资2"?
可以在一个查询中完成。考虑:
条件表达式:
SELECT Avg(IIf(EducationLevel = 1, Salary, Null)) AS AverageSalary,
Avg(IIf(EducationLevel = 2, Salary, Null)) AS AverageSalary2
FROM [Table];
或CROSSTAB:
TRANSFORM Avg(Table.Salary) AS AvgOfSalary
SELECT 1 AS R
FROM [Table]
GROUP BY 1
PIVOT "AverageSalary" & [EducationLevel];
或者构建一个报告并使用它的Sorting&使用聚合计算对要素进行分组。这样可以方便地显示详细记录以及摘要数据。
Below query, if the educational level field is a number field
SELECT Avg(Switch([EducationalLevel]=1,[salary])) AS AverageSalary,
Avg(Switch([EducationalLevel]=2,[salary])) AS AverageSalary2
FROM salary;
以下查询educationallevel字段是否为文本字段
SELECT Avg(Switch([EducationalLevel]="1",[salary])) AS AverageSalary,
Avg(Switch([EducationalLevel]="2",[salary])) AS AverageSalary2
FROM salary;