查询，显示哪个经理拥有最多的员工

Question

我正在使用SQL server管理工作室，并且需要找到哪个经理拥有最多的员工。

我希望输出显示John Wick是最高的

EmployeeID |  Numberofemployees
-------------------------------
1      |         4
CREATE TABLE Employee (
enmployeeID int PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
JobTitle varchar(50) NOT NULL,
Manager int,
);
INSERT INTO Employee VALUES (1, 'John', 'Wick', 'Manager', 2)
INSERT INTO Employee VALUES (2, 'Danny', 'Glover', 'Chairman', NULL)
INSERT INTO Employee VALUES (3, 'Sam', 'Smith', 'Manager', 2)
INSERT INTO Employee VALUES (4, 'Denise', 'Richards', 'Agent', 3)
INSERT INTO Employee VALUES (5, 'Tanner', 'Jones', 'Agent', 3)
INSERT INTO Employee VALUES (6, 'Danie', 'Wide', 'Admin', 1)
INSERT INTO Employee VALUES (7, 'Sue', 'Hobs', 'Admin', 1)
INSERT INTO Employee VALUES (8, 'Tommy', 'Laker', 'Accounts', 1)
INSERT INTO Employee VALUES (9, 'Clarence', 'Kennedy', 'Admin', 1)

这是我目前想到的:

SELECT Count(employeeID), Manager
From Employee
HAVING COUNT (Manager)=( 
SELECT MAX(Manager)
Group By employeeID 

Answer 1

根据您的数据，您是否可以尝试以下查询

SELECT TOP 1 X.Manager,X.CNTT
FROM
(
SELECT E.MANAGER,COUNT(E.enmployeeID)CNTT
FROM Employee AS E
GROUP BY E.Manager 
)X ORDER BY X.CNTT DESC 

Answer 2

这是一种方法:

SELECT *
FROM (
SELECT man.employeeID
, man.FirstName
, man.LastName, COUNT(*) AS countEmployees
, RANK() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM Employee AS emp
INNER JOIN Employee AS man ON emp.Manager = man.employeeID
GROUP BY man.employeeID, man.FirstName, man.LastName
) t
WHERE t.rn = 1

演示

Answer 3

或者使用

SELECT TOP 1 b.lastName,count(*) as numberOfEmployees FROM Employee as a 
LEFT JOIN Employee as b ON a.Manager=b.employeeID
-- LEFT JOIN Employee as c ON b.Manager=c.employeeID
WHERE b.JobTitle='Manager'
GROUP BY b.LastName
ORDER BY numberOfEmployees DESC

Answer 4

此解决方案向下递归并计算所有员工，甚至包括较低级管理人员的员工

WITH cte AS (
SELECT E.enmployeeID, E.MANAGER
FROM Employee AS E
JOIN Employee AS M ON M.enmployeeID = E.MANAGER
WHERE M.JobTitle = 'Manager'
UNION ALL
SELECT E.enmployeeID, E.MANAGER
FROM Employee AS E
JOIN cte ON cte.enmployeeID = E.MANAGER
)
SELECT TOP (1) Manager,COUNT(*)
FROM cte
GROUP BY Manager 
ORDER BY COUNT(*) DESC;