SQL单表按ID筛选仅含sent和空status、排除delivered的数据查询问题

需求说明 #

需要筛选出满足以下条件的message_thread_id：

• 该ID对应的所有记录中，不存在status为delivered的记录
• 该ID对应的所有记录的status仅能为sent或者空值/NULL

实现代码 #

假设目标表名为message_records，可选用以下两种实现方式：

方式1：聚合筛选（推荐，单表扫描性能更高） #

SELECT message_thread_id
FROM message_records
GROUP BY message_thread_id
HAVING
  -- 无delivered状态的记录
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) = 0
  -- 所有记录状态仅为sent或空
  AND COUNT(CASE WHEN status != 'sent' AND status IS NOT NULL AND status != '' THEN 1 END) = 0

方式2：子查询筛选 #

SELECT DISTINCT t.message_thread_id
FROM message_records t
WHERE NOT EXISTS (
  SELECT 1 FROM message_records t1
  WHERE t1.message_thread_id = t.message_thread_id
  AND t1.status = 'delivered'
)
AND NOT EXISTS (
  SELECT 1 FROM message_records t2
  WHERE t2.message_thread_id = t.message_thread_id
  AND t2.status != 'sent' 
  AND t2.status IS NOT NULL 
  AND t2.status != ''
)

样例运行结果 #

针对提供的测试数据，最终筛选出的符合条件的ID为：

• 240
• 1044

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