单表按相同ID多条件查询：匹配多类status状态的SQL实现问题

查询responded类message_thread_id #

需求是匹配同时存在sent、空字符串、delivered三种状态的线程ID，使用分组聚合判断实现，语句如下：

SELECT message_thread_id
FROM 表名 -- 替换为实际使用的表名
GROUP BY message_thread_id
HAVING
  SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN status = '' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) > 0;

基于提供的测试数据，该查询返回结果为229。

查询pending类message_thread_id #

需求是匹配存在sent、delivered状态，且不存在空状态的线程ID，语句如下：

SELECT message_thread_id
FROM 表名 -- 替换为实际使用的表名
GROUP BY message_thread_id
HAVING
  SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN status = '' THEN 1 ELSE 0 END) = 0;

基于提供的测试数据，无符合条件的返回结果。

兼容MySQL的简化写法 #

如果使用MySQL数据库，可以利用布尔值隐式转换的特性简化HAVING条件，语句更精简：

• responded查询简化版：

SELECT message_thread_id
FROM 表名
GROUP BY message_thread_id
HAVING MAX(status = 'sent') AND MAX(status = '') AND MAX(status = 'delivered');

• pending查询简化版：

SELECT message_thread_id
FROM 表名
GROUP BY message_thread_id
HAVING MAX(status = 'sent') AND MAX(status = 'delivered') AND MAX(status = '') = 0;

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