如何用GROUP BY HAVING子句筛选至少含1个'in'状态的ID

表结构与样例数据 #

现有status表，创建语句及样例数据如下：

create table status(id, status)as values
 (1,    'in')
,(1,    'out')
,(1,    'in')
,(1,    'in')
,(2,    'in')
,(2,    'in')
,(3,    'out')
,(3,    'out')
,(3,    'in')
,(3,    'out')
,(4,    'out')
,(4,    'out');

需求与原始问题 #

需求是筛选出至少包含1个'in'状态的ID，最初编写的SQL如下：

SELECT id, COUNT(status) 
FROM status  
GROUP BY id
HAVING count(*) > 1

这段SQL的问题在于：HAVING count(*) > 1仅筛选出状态记录数大于1的ID，完全未关联'in'状态的判断，因此会包含像ID 4这种没有任何'in'状态的记录。

正确的SQL写法 #

方法一：基于聚合函数筛选特定状态 #

通过统计每个ID下状态为'in'的记录数，只要数量大于0即可：

SELECT id, COUNT(status) AS total_status_count
FROM status  
GROUP BY id
HAVING COUNT(CASE WHEN status = 'in' THEN 1 END) > 0;

也可以用SUM函数实现相同逻辑：

SELECT id, COUNT(status) AS total_status_count
FROM status  
GROUP BY id
HAVING SUM(CASE WHEN status = 'in' THEN 1 ELSE 0 END) > 0;

方法二：用EXISTS子查询判断 #

通过子查询直接判断当前ID是否存在'in'状态的记录，逻辑更直观：

SELECT DISTINCT id
FROM status s
WHERE EXISTS (
    SELECT 1 
    FROM status 
    WHERE id = s.id AND status = 'in'
);

如果不需要统计总状态数，这种写法在大表场景下效率可能更高。

方法三：用IN子查询匹配ID #

先提取所有包含'in'状态的ID，再去重返回：

SELECT DISTINCT id
FROM status
WHERE id IN (SELECT id FROM status WHERE status = 'in');

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