如何编写无重复结果的同表记录比较SQL查询？

针对你用自联表查询重复customer_number时出现反向重复行的问题，最简单的优化方式就是把查询条件里的a1.id <> a2.id改成a1.id < a2.id，这样就能确保每一组重复的记录对只返回一次，不会出现(x,y)和(y,x)的重复结果。

优化后的SQL语句如下：

select a1.id, a1.customer_name, a1.customer_number, a2.id, a2.customer_name, a2.customer_number
from account a1 join account a2 on a1.customer_number = a2.customer_number
where a1.id < a2.id;

原理很直接：通过限定a1.id小于a2.id，我们只保留每个重复组中ID更小的记录和更大的记录的配对，完全避免了反向匹配的情况。

如果你的场景需要先找出所有存在重复的customer_number，再关联查看对应的记录，也可以用窗口函数先标记重复项，再进行查询，比如：

with duplicate_accounts as (
    select *,
           count(*) over (partition by customer_number) as cnt
    from account
)
select da1.id, da1.customer_name, da1.customer_number, da2.id, da2.customer_name, da2.customer_number
from duplicate_accounts da1
join duplicate_accounts da2 on da1.customer_number = da2.customer_number
where da1.id < da2.id and da1.cnt > 1;

这种方式先筛选出有重复的账户，再进行自联，在数据量较大时可能会有性能上的提升。

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