sql - 在 Count 聚合函数上使用 Min 聚合函数

Question

我使用了这个查询:

 select D.DeptID, D.Dept, count(E.EmployeeID) as TotalStaff
 from Employees as E
 right join Departments as D
 on D.DeptID = E.DeptID 
 group by D.DeptID, D.Dept;

要返回这个:

DeptID_|_Dept___________|TotalStaff
40     | Marketing      | 2
50     | Accounting     | 3
60     | Manager        | 3
70     | GeneralStaff   | 1
80     | HumanResources | 1
90     | Production     | 0
100    | Sales          | 0

现在我想列出员 worker 数最少的部门的部门 ID、部门和员 worker 数，所以我尝试了这个:

SELECT MIN(mycount) 
FROM 
(
 select D.DeptID, count(E.EmployeeID) as mycount
 from Employees as E
 right join Departments as D
 on D.DeptID = E.DeptID 
 group by D.DeptID
);

但是我收到一条错误消息:';' 附近的语法不正确。
我想要做的就是返回员 worker 数最少的部门。请任何人都可以帮我解决这个问题。

Answer 1

请尝试使用以下查询:

对于最小值:

 select min(A.mycount) as min_count
    from  (
         select D.DeptID, count(E.EmployeeID) as mycount
         from Departments as D
         left outer join Employees as E on D.DeptID = E.DeptID 
         group by D.DeptID
    ) as A

对于最大值:

select max(A.mycount) as max_count
from  (
     select D.DeptID, count(E.EmployeeID) as mycount
     from Departments as D
     left outer join Employees as E on D.DeptID = E.DeptID 
     group by D.DeptID
) as A