如何去除SQL笛卡尔积中两列值交替的重复行？

这个问题很常见，当你对同一张表做笛卡尔积关联并且用g1.gen_id <> g2.gen_id排除自身时，就会出现(A,B)和(B,A)这样的成对重复组合。要把结果精简到一半行数，这里有两种可行的方案，优先推荐第一种：

方案1：修改WHERE条件，提前过滤重复组合（高效首选） #

把原来的g1.gen_id <> g2.gen_id替换成g1.gen_id < g2.gen_id（或者g1.gen_id > g2.gen_id，根据你想要保留的顺序选择）。这样就能保证每一对不同的gen_id组合只会出现一次，直接在查询阶段就过滤掉了反向的重复行，性能最优。

修改后的完整SQL如下：

select g1.gen_id as 'gen_1', g2.gen_id as 'gen_2', count(*) as 'count' 
from gen g1, gen g2, dir d 
where g1.gen_id < g2.gen_id 
  [other irrelevant where conditions here] 
order by g1.gen_id, g2.gen_id;

比如原来的('32','34')和('34','32')会变成只保留('32','34')（如果用<），正好把442行精简到221行。

方案2：用窗口函数事后去重（适合已生成结果集的场景） #

如果因为某些原因无法修改原始查询的WHERE条件，也可以用窗口函数对结果集进行事后去重。通过LEAST()和GREATEST()函数把每对组合转换成固定的分组键，然后给每组的行编号，只保留编号为1的行：

WITH cte AS (
    select g1.gen_id as 'gen_1', g2.gen_id as 'gen_2', count(*) as 'count',
           -- 按每对的最小和最大ID分组，给每组行编号
           ROW_NUMBER() OVER (PARTITION BY LEAST(g1.gen_id, g2.gen_id), GREATEST(g1.gen_id, g2.gen_id) ORDER BY g1.gen_id) as rn
    from gen g1, gen g2, dir d 
    where g1.gen_id <> g2.gen_id 
      [other irrelevant where conditions here] 
)
SELECT gen_1, gen_2, count
FROM cte
WHERE rn = 1
ORDER BY gen_1, gen_2;

这种方法会先生成所有442行数据，再过滤掉一半，性能比方案1差一些，但适合无法修改原始查询逻辑的场景。

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