SQL自连接比较返回重复结果，如何避免重复比较？

这个问题我经常碰到，本质是因为你用<> 做关联条件时，会生成互为镜像的对称结果——比如(a,b)和(b,a)其实是同一个比较的正反两面，我们只需要保留其中一面就行。给你几个实用的解决办法：

方法1：利用值的大小关系过滤（最简洁） #

直接把a.value <> b.value替换成a.value < b.value，这样只会保留字典序（或数值序）更小的value在前的组合，自动排除镜像重复：

select distinct a.id, b.id, a.value, b.value 
from tbl a, tbl b 
where a.id = b.id and a.value < b.value

执行后你会得到干净的结果：

id  id  value  value
1   1   a      b
2   2   c      d

方法2：用行号区分（适用于非可比较类型的value） #

如果你的value字段是无法直接比较大小的类型（比如二进制、复杂字符串），可以先给每个ID分组内的行加行号，再通过行号的大小来过滤重复：

with ranked_tbl as (
    select id, value, row_number() over(partition by id order by value) as rn
    from tbl
)
select distinct a.id, b.id, a.value, b.value
from ranked_tbl a
join ranked_tbl b on a.id = b.id and a.rn < b.rn

这里的row_number()会给每个ID下的value按顺序分配唯一行号，我们只关联行号更小的a和行号更大的b，确保每个组合只出现一次。

额外优化：先去重再关联（提升查询性能） #

你的原始数据里存在重复的(id,value)组合（比如id=1的两个a），如果先对表做去重，再进行关联，可以省去最终结果的distinct，提升查询效率：

with unique_tbl as (
    select distinct id, value from tbl
)
select a.id, b.id, a.value, b.value
from unique_tbl a
join unique_tbl b on a.id = b.id and a.value < b.value

这个写法的结果和方法1完全一致，但因为提前过滤了重复行，关联时的计算量会小很多，适合数据量较大的场景。

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