如何在T-SQL中实现排除自配对的表自连接？

有时候我们需要获取表中所有行的配对（即表自身的笛卡尔积），可以通过自连接实现：

drop table if exists #a
create table #a (x int)
insert into #a values (1),(2),(3)
select *
from #a a1
join #a a2
  on 0 = 0 -- 恒成立的连接条件

这段代码会返回9行结果。

当列x是唯一标识时，要排除行与自身的配对，只需添加a1.x != a2.x的条件：

select *
from #a a1
join #a a2
  on a1.x != a2.x

此时返回6行结果。

但如果x不唯一，比如执行以下语句更新数据后：

delete from #a
insert into #a values (4), (4), (5)

表中有两行值为4的独立行，我们需要排除行自身的配对（而非所有x值相等的配对），期望得到结果：(4, 4), (4, 5), (4, 4), (4, 5), (5, 4), (5, 4)，共6行。

原生T-SQL解决方案 #

可以利用窗口函数ROW_NUMBER()为每行生成临时唯一行标识，通过比较行标识排除自配对，无需修改表结构添加物理列：

select a1.x, a2.x
from (
    select x, ROW_NUMBER() over (order by (select null)) as rn
    from #a
) a1
join (
    select x, ROW_NUMBER() over (order by (select null)) as rn
    from #a
) a2
on a1.rn != a2.rn

这里order by (select null)用于在不指定固定排序规则的情况下生成行号（若有实际排序需求，可替换为对应字段），通过a1.rn != a2.rn确保不会出现同一行的自配对，同时保留不同行但x值相同的配对结果，正好符合需求返回6行。

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