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

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

这在最后一个选择语句中给了我一个错误，请帮助:)

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)
;

标签： mysqlms-access

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