如何在 WHERE 子句中使用 COUNT、COUNTIF 或 SUM?

Question

>我有一个员工表，我正在尝试获取没有活跃员工的部门 ID 列表。

我的第一次尝试是：

SELECT DISTINCT DivisionID
FROM Employees
WHERE COUNT(IsActive = 0) = 0
GROUP BY DivisionID

但是Count不允许表达，即使智能感知说它允许。

所以我尝试了：

SELECT DISTINCT DivisionID
FROM Employees
WHERE SUM(CAST(IsActive AS INT)) = 0
GROUP BY DivisionID

但随后它抱怨"聚合可能不会出现在 WHERE 子句中，除非它位于 HAVING 子句或选择列表中包含的子查询中，并且正在聚合的列是外部引用。

这似乎应该是直截了当的，不确定问题是什么。

Answer 1

使用having，您可以使用条件聚合筛选分组的记录

SELECT DivisionID
FROM Employees    
GROUP BY DivisionID
HAVING sum(case when IsActive = 1 then 1 else 0 end) = 0

Answer 2

我怀疑这能满足你的要求：

SELECT DivisionID
FROM Employees
GROUP BY DivisionID
HAVING MAX(IsActive) = 0

Answer 3

SELECT DivisionID
from (
SELECT DivisionID, count(*) as tmpcount from Employees WHERE IsActive=0 group by 
DivisionID 
) 
WHERE tmpcount>0