SQL实现：获取含指定Keyname且不含另一Keyname的MessageID

假设你的数据表名为message_data，以下几种写法都能查询出**存在Keyname为"ab"但不存在Keyname为"kk"**的MessageID，预期返回Second：

方法1：NOT EXISTS（推荐，性能优） #

直接筛选有ab记录且无对应kk记录的MessageID：

SELECT DISTINCT m.MessageID
FROM message_data m
WHERE m.Keyname = 'ab'
  AND NOT EXISTS (
    SELECT 1
    FROM message_data m2
    WHERE m2.MessageID = m.MessageID
      AND m2.Keyname = 'kk'
  );

方法2：LEFT JOIN 筛选空值 #

通过左连接自身，过滤掉有kk记录的行：

SELECT DISTINCT m.MessageID
FROM message_data m
LEFT JOIN message_data m2
  ON m.MessageID = m2.MessageID
  AND m2.Keyname = 'kk'
WHERE m.Keyname = 'ab'
  AND m2.MessageID IS NULL;

方法3：GROUP BY + HAVING 统计分组 #

按MessageID分组，统计是否包含ab且不包含kk：

SELECT MessageID
FROM message_data
WHERE Keyname IN ('ab', 'kk')
GROUP BY MessageID
HAVING COUNT(CASE WHEN Keyname = 'ab' THEN 1 END) > 0
   AND COUNT(CASE WHEN Keyname = 'kk' THEN 1 END) = 0;

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