sql - 如何根据他的状态选择不同的数字

Question

有这样的表:

create table test(num integer,state text,col1 integer);

和示例数据:

insert into test(num,state,col1) values(1234,'done',1),(1234,'done',2),(1234,'done',11),
(23123,'done',1),(23123,'notdone',1),(23123,'any_other_states',2131);

想要从这个表中选择不同的数字，其中状态只完成，例如输出必须是:1234我尝试这样的事情:

SELECT DISTINCT num
FROM test
WHERE state = 'done' and state not in('notdone','any_other_states')
GROUP BY num;

但是输出是1234和23123，怎么办？

Answer 1

在 PostgreSQL 中:

select num
from test
group by num
having bool_and(state = 'done')

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE