我在 SQL 中有兩個用于 MS Access 的查詢。第一個是：
SELECT AVG(Salary) AS AverageSalary FROM Table WHERE EducationLevel = 1;
第二個查詢：
SELECT AVG(Salary) AS AverageSalary2 FROM Table WHERE EducationLevel = 2;
如何將它們組合到一個查詢中以生成包含兩列的表，分別標記為“AverageSalary”和“AverageSalary2”？
uj5u.com熱心網友回復：
可以一次查詢完成。考慮：
條件運算式：
SELECT Avg(IIf(EducationLevel = 1, Salary, Null)) AS AverageSalary,
Avg(IIf(EducationLevel = 2, Salary, Null)) AS AverageSalary2
FROM [Table];
或交叉表：
TRANSFORM Avg(Table.Salary) AS AvgOfSalary
SELECT 1 AS R
FROM [Table]
GROUP BY 1
PIVOT "AverageSalary" & [EducationLevel];
或者構建一個報告并使用其排序和分組功能進行聚合計算。這很容易允許顯示詳細記錄以及匯總資料。
uj5u.com熱心網友回復：
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;
下面查詢educationlevel欄位是否為文本欄位
SELECT Avg(Switch([EducationalLevel]="1",[salary])) AS AverageSalary,
Avg(Switch([EducationalLevel]="2",[salary])) AS AverageSalary2
FROM salary;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/410448.html
標籤：
上一篇：在ms訪問中按組選擇前x