SQL去重：如何去除用户双内容流派组合查询中的重复行

有几种实用的思路可以去除A-B和B-A这类重复的流派组合：

思路1：从连接源头避免重复 #

把自连接的条件从a.content_genre <> b.content_genre改成a.content_genre < b.content_genre（或者>，保持一致即可），这样只会生成字典序靠前的流派在前的组合，从根源上避免双向重复。同时把LEFT JOIN换成INNER JOIN，因为我们只需要同时观看两种流派的用户：

select genre1, genre2, count(uid) as User_count 
from (
  select a.uid, a.content_genre as genre1, b.content_genre as genre2 
  from (
      select content_genre, uid 
      from table 
      group by 1, 2
  ) a
  inner join ( 
    select content_genre, uid 
    from table 
    group by 1, 2
  ) b on a.uid = b.uid and a.content_genre < b.content_genre
)
group by 1,2 
order by user_count desc

思路2：统一流派组合的顺序 #

如果不想修改连接逻辑，可以用排序函数把两个流派按固定顺序排列，再分组统计。比如用least()和greatest()（不同数据库语法可能略有差异，比如SQL Server用IIF(genre1 < genre2, genre1, genre2)），同时注意用count(distinct uid)避免同一个用户被重复统计：

select 
  least(genre1, genre2) as first_genre, 
  greatest(genre1, genre2) as second_genre, 
  count(distinct uid) as User_count 
from (
  select a.uid, a.content_genre as genre1, b.content_genre as genre2 
  from (
      select content_genre, uid 
      from table 
      group by 1, 2
  ) a
  inner join ( 
    select content_genre, uid 
    from table 
    group by 1, 2
  ) b on a.uid = b.uid and a.content_genre <> b.content_genre
)
group by first_genre, second_genre
order by User_count desc

思路3：过滤已有结果中的重复组合 #

如果已经生成了包含重复组合的结果集，可以在外层查询添加过滤条件，只保留genre1小于genre2的行：

select genre1, genre2, User_count
from (
  select genre1, genre2, count(uid) as User_count 
  from (
    select a.uid, a.content_genre as genre1, b.content_genre as genre2 
    from (
        select content_genre, uid 
        from table 
        group by 1, 2
    ) a
    inner join ( 
      select content_genre, uid 
      from table 
      group by 1, 2
    ) b on a.uid = b.uid and a.content_genre <> b.content_genre
  )
  group by 1,2 
) t
where genre1 < genre2
order by User_count desc

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