MySQL如何筛选仅出现一次的多列组合对应行

需求说明 #

现有一张包含KEY1、KEY2、KEY3、KEY6、PLT_ID列的表，存在同一(KEY1,KEY2,KEY3,PLT_ID)组合对应多个不同KEY6值的行，需要筛选出仅对应一个KEY6值的(KEY1,KEY2,KEY3,PLT_ID)组合的所有行。

重复组合示例 #

测试数据集 #

期望结果 #

原因：这两行的(KEY1,KEY2,KEY3,PLT_ID)组合在表中仅对应一个KEY6值，属于唯一组合。

原SQL的问题 #

用户尝试的语句：

SELECT KEY1, KEY2, KEY3, KEY6, PLT_ID FROM RAWDATA WHERE KEY6 = 'Outbound' GROUP BY KEY1, KEY2, KEY3, KEY6, PLT_ID HAVING (COUNT(KEY1) = 1 AND COUNT(KEY2)  = 1 AND COUNT(KEY6)  = 1 AND COUNT(PLT_ID)  = 1);

核心问题：

1. 分组时包含了KEY6，导致每个不同的KEY6都会单独成组，无法统计同一(KEY1,KEY2,KEY3,PLT_ID)组合下的KEY6数量。
2. COUNT(KEY1)等统计的是组内非空值的数量，而非该组合在表中的总出现次数，逻辑完全错误。

正确的MySQL查询语句 #

方法1：子查询关联（兼容所有MySQL版本） #

先统计每个(KEY1,KEY2,KEY3,PLT_ID)组合的出现次数，再筛选出次数为1的组合对应的行：

SELECT r.KEY1, r.KEY2, r.KEY3, r.KEY6, r.PLT_ID
FROM RAWDATA r
INNER JOIN (
    SELECT KEY1, KEY2, KEY3, PLT_ID
    FROM RAWDATA
    -- 如需保留原WHERE条件（如KEY6='Outbound'），可添加在此处
    -- WHERE KEY6 = 'Outbound'
    GROUP BY KEY1, KEY2, KEY3, PLT_ID
    HAVING COUNT(*) = 1
) t ON r.KEY1 = t.KEY1 
   AND r.KEY2 = t.KEY2 
   AND r.KEY3 = t.KEY3 
   AND r.PLT_ID = t.PLT_ID
-- 如需保留原WHERE条件，也可添加在此处
-- WHERE r.KEY6 = 'Outbound'
;

方法2：窗口函数（MySQL 8.0+支持） #

使用窗口函数直接计算每个组合的出现次数，再筛选：

SELECT KEY1, KEY2, KEY3, KEY6, PLT_ID
FROM (
    SELECT 
        KEY1, KEY2, KEY3, KEY6, PLT_ID,
        COUNT(*) OVER (PARTITION BY KEY1, KEY2, KEY3, PLT_ID) AS cnt
    FROM RAWDATA
    -- 如需保留原WHERE条件，可添加在此处
    -- WHERE KEY6 = 'Outbound'
) t
WHERE cnt = 1;

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