在SQL查询中使用GROUP BY的问题

Question

我试图在MS SQL Server中编写一个propper SQL查询。首先，我有以下表格：城镇，员工，地址。几乎每个员工都有Manager，ManagerID也是员工的外键。（自我关系）。我的目标是显示每个镇的经理人数。到目前为止，我有以下代码：

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID

GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Town          | Managers from each town
Issaquah      | 3
Kenmore       | 5
Monroe        | 2
Newport Hills | 1

Answer 1

如果我正确理解您的结构，那么员工是经理的唯一指示是其id是否被用作其他员工的managerid。您的第一个查询已经正确地显示了计数，然后所需要的就是用以下内容筛选结果：

where EmployeeID in (select ManagerID from Employees)

因此，将第一个查询转换为：

SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
where EmployeeID in (select ManagerID from Employees)
GROUP BY t.Name
ORDER BY [Managers from each town] DESC