SQL查询：如何筛选满足双状态条件并按parent_id分组的parent_ids

错误原因 #

你之前的写法存在逻辑错误：单条数据的status字段只能存储一个值，不可能同时等于两个不同的状态，所以status = ('processing' and 'pending')这类条件永远不成立，你之前匹配到的只是符合任意单个状态的数据，完全达不到多状态同时匹配的要求。

正确SQL写法 #

要实现同一个parent_id下同时存在多个指定状态的需求，需要分组后用HAVING结合聚合函数做条件判断，写法如下：

SELECT parent_id 
FROM tableName
-- 提前过滤无关状态，降低后续计算开销
WHERE status IN ('processing', 'pending', 'canceled')
GROUP BY parent_id
-- 分组后判断是否满足两组状态组合的其中一组
HAVING 
  -- 组合1：同时存在processing和pending
  (COUNT(DISTINCT CASE WHEN status IN ('processing', 'pending') THEN status END) = 2)
  OR
  -- 组合2：同时存在processing和canceled
  (COUNT(DISTINCT CASE WHEN status IN ('processing', 'canceled') THEN status END) = 2);

如果你的业务中同一个parent_id下同一个status不会出现重复记录，可以去掉COUNT里的DISTINCT，查询性能会更高。

逻辑说明 #

• 先用WHERE子句过滤掉不需要的状态，只保留我们需要判断的三个状态，减少后续聚合计算的量级
• 按parent_id分组后，通过CASE语句匹配对应状态组合，统计不同状态的数量，数量等于2就说明这个parent_id下同时存在对应组合的两个状态
• 两组条件满足任意一组就会被筛选出来，完全匹配你的需求

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