PostgreSQL中基于status及ka匹配关系筛选符合条件的ID列表

我们有如下数据表：

create table card(id int,ka text,status int,modifiedon date);
insert into card values
(1,'ka1',1,'2023-11-13'),
(2,'ka2',1,'2023-11-13'),
(3,'ka2',0,'2023-11-15'),
(4,'ka3',1,'2023-11-13'),
(5,'ka4',1,'2023-11-13');

需要查询满足以下条件的id：

• 仅显示status为1的记录
• 如果同一ka值下存在status为0的记录，则该ka对应的所有记录都不显示

预期结果 #

解决方案 #

方法一：子查询排除存在status=0的ka值 #

SELECT c.id
FROM card c
WHERE c.status = 1
AND c.ka NOT IN (
    SELECT ka
    FROM card
    WHERE status = 0
);

方法二：分组聚合筛选合法ka值 #

先通过分组判断每个ka是否存在status=0的记录，再关联原表获取目标数据：

SELECT c.id
FROM card c
JOIN (
    SELECT ka
    FROM card
    GROUP BY ka
    HAVING MIN(status) != 0
) k ON c.ka = k.ka
WHERE c.status = 1;

内容的提问来源于stack exchange，提问作者Tirta Salsabila