sql - 根据其他列值按查询分组

Question

假设我有一个名为 process_states 的表:

+--------+--------+
|Process | State  |
-------------------
|   A    |   0    |
|   A    |   0    |
|   B    |   0    |
|   B    |  -1    |
|   C    |  -99   |
-------------------
Note: State can have many more negative and positive state values

I want to find all processes having all rows with state 0. In the above case I want to get A.

我正在尝试使用 group by 来做，有没有办法做这样的事情:

select process from process_states 
group by process 
having <all state for that process is 0>

是否可以使用 group by 来做到这一点？

Answer 1

您可以使用 group by 子句并使用 having 子句过滤进程:

SELECT   process
FROM     process_states
GROUP BY process
HAVING   COUNT(CASE state WHEN -1 THEN 1 END) = 0

编辑:

鉴于对其他答案的澄清评论，如果要求仅查找只有 0 状态的进程，您可以使用 计算总行数和行数0 状态并比较它们:

SELECT   process
FROM     process_states
GROUP BY process
HAVING   COUNT(CASE state WHEN 0 THEN 1 END) = COUNT(*)