mysql - Access super 卡住表

Question

我一直在尝试合并两个表，但无法完成。尝试将员工数量添加到另一个表中。

SELECT [Training History].JobID,(
       SELECT Job.Department 
       FROM Job 
       WHERE [Training History].JobID = Job.JobID) 
       AS Department, 
SUM([Expenditure]) AS ExpenditureOfJob 
     FROM [Training History] 
     GROUP BY [Training History].JobID,
(SELECT COUNT (*) AS [Number of Employees] 
     FROM Employee 
     GROUP BY JobID)
;

还尝试过；

(SELECT COUNT (*) AS [Number of Employees] 
     FROM Employee 
    WHERE [Training History].JobID = Employee.JobID
 GROUP BY JobID)

说最后一点有语法错误，但我找不到它。

Answer 1

由于您正在运行不同的分组级别聚合查询(首先按JobID和部门，然后仅按JobID)，请考虑加入它们作为派生表(即 FROM 或 JOIN 子句中的嵌套子查询):

SELECT agg1.JobID, agg1.Department, agg1.ExpenditureOfJob, agg2.[Number of Employees]
FROM
  (SELECT t.JobID, j.Department, SUM([Expenditure]) AS ExpenditureOfJob 
   FROM [Training History] t
   INNER JOIN Job j ON t.JobID = j.JobID
   GROUP BY t.JobID, j.Department) As agg1

   INNER JOIN
  (SELECT t.JobID, Count(*) AS [Number of Employees]
   FROM [Training History] t
   GROUP BY t.JobID) As agg2

ON agg1.JobID = agg2.JobID

或者加入您已保存的查询:

SELECT agg1.*, agg2.[Number of Employees]
FROM
  mySavedQueryAgg1 as agg1

INNER JOIN
  mySavedQueryAgg2 as agg2

ON agg1.JobID = agg2.JobID