SQL查询使用DISTINCT未去除重复值的原因及解决方案咨询

问题原因分析 #

DISTINCT的作用逻辑是对所有被查询列的组合做去重，而非单独作用于某一列。你同时查询了friend_id和friend_hobby，只要两个字段的组合存在差异，就会被判定为不同的行保留下来，所以才会出现同一个friend_id对应多条不同hobby的结果，你判断的原因是正确的，确实和同时查询hobbies字段有关。

可选解决方案 #

你需要每个friend_id仅返回一行，且hobby值不影响后续使用，可以用以下几种合规方案，均不需要使用无聚合的GROUP BY：

方案1：窗口函数（兼容所有支持标准SQL的数据库） #

用ROW_NUMBER()按friend_id分区，取每组任意一条记录即可：

SELECT friend_id, friend_hobby
FROM (
    SELECT 
        friend_id, 
        hobbies as friend_hobby,
        ROW_NUMBER() OVER (PARTITION BY friend_id) as rn
    FROM all_friends
    WHERE friend_id NOT IN 
         (SELECT DISTINCT(friend_id)
          FROM all_friends
          WHERE friend_type IN ('Good', 'Great'))
) t
WHERE rn = 1;

方案2：DISTINCT ON（适用于PostgreSQL数据库） #

PostgreSQL支持针对指定字段去重的DISTINCT ON语法，直接指定按friend_id去重即可：

SELECT DISTINCT ON (friend_id) friend_id, hobbies as friend_hobby
FROM all_friends
WHERE friend_id NOT IN 
     (SELECT DISTINCT(friend_id)
      FROM all_friends
      WHERE friend_type IN ('Good', 'Great'));

方案3：带聚合函数的GROUP BY（完全符合SQL规范） #

你之前抵触的是无聚合的GROUP BY，这种带聚合的用法是合规的，只需对hobby字段做简单聚合即可：

SELECT friend_id, MAX(hobbies) as friend_hobby
FROM all_friends
WHERE friend_id NOT IN 
     (SELECT DISTINCT(friend_id)
      FROM all_friends
      WHERE friend_type IN ('Good', 'Great'))
GROUP BY friend_id;

这里用MAX或者MIN都可以，不影响后续使用。

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