sql - 列 '*' 在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中

Question

我有一个如下查询

select a.EmployeeName, 

STUFF(( SELECT ',' + camTable.DepartmentName AS [text()]
                        FROM EmpoyeeDepartment subTable
                left join DepartmentTable camTable on subTable.DepartmentID = camTable.DepartmentID 
                        WHERE
                        subTable.EmployeeID = a.EmployeeID
                        FOR XML PATH('')
                        ), 1, 1, '' )
            AS Departments
from 
EmployeeTable a
where a.EmployeeID = 144025
group by EmployeeName, Departments

但是当我执行上面的sql时，出现错误:

Column 'EmployeeTable.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

预期结果:

上面的sql有什么问题？

Answer 1

我认为这是因为 group by 子句中缺少 EmployeeID 列。子查询中指定的列也应包含在 group by 子句中。

请尝试:

select a.EmployeeName, 

STUFF(( SELECT ',' + camTable.DepartmentName AS [text()]
                        FROM EmpoyeeDepartment subTable
                left join DepartmentTable camTable on subTable.DepartmentID = camTable.DepartmentID 
                        WHERE
                        subTable.EmployeeID = a.EmployeeID
                        FOR XML PATH('')
                        ), 1, 1, '' )
            AS Departments
from 
EmployeeTable a
where a.EmployeeID = 144025
group by EmployeeID, EmployeeName, Departments