mysql - 尝试合并表时出现 Access 错误

Question

抱歉，如果我是新手，这真的很简单。我一直在尝试合并两张 table ，但无法完成。尝试将员工数量添加到另一个表中。

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 [Number of Employees] 
    FROM Q9r, 
    WHERE Q9r.JobID = [Training History].JobID
    )
;

这在最后一个 select 语句中给了我一个错误，请帮忙:)

Q9r表代码是；

SELECT JobID, COUNT (*) AS [Number of Employees] FROM Employee GROUP BY JobID;
enter code here

也无法让它工作(这是我更喜欢的方法，因为它可以在单个表中完成)

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 [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 
    WHERE [Training History].JobID = Employee.JobID
 GROUP BY JobID)
;

Answer 1

您可以通过这种方式轻松组合 2 个表格:

<小时/>

-- My way to combinates 2 tables:
SELECT Department.JobID, Department.NameDepartment,
       Department.ExpenditureOfJob, Job.NumberOfEmployees 
FROM Department INNER JOIN Job 
ON Department.JobID = Job.JobID
GROUP BY Department.JobID;

顺便说一句，您的查询可以适应这种形式:

-- I suppose that the Training History & Expenditure both are the Databases_Name... 
SELECT [Training History].JobID, Job.Department AS Department, SUM([Expenditure]) AS ExpenditureOfJob 
FROM [Training History].Department INNER JOIN [Training History].JobID 
GROUP BY [Training History].JobID,( SELECT COUNT (*) AS [Number of Employees] 
                                    FROM Employee 
                                    GROUP BY JobID);

无论如何，我将测试留在 SQL Fiddle 中