如何删除SQL查询结果表中的反向重复数据，仅保留单条正向记录

现有基础信息 #

表A #

source
a
b
c
d

表B #

destination
b
a
d
c

当前关联查询逻辑 #

你目前通过行号对齐两张表的查询语句如下：

with A as(
select row_number() over() idx, source from a 
),
B as (
select row_number() over() idx, destination from b
),
C as (
select A.source, B.destination from A join B on A.idx=B.idx
)

select * from C;

得到的查询结果为：

source  destination
a        b
b        a
c        d
d        c

解决方案 #

要删除反向重复记录，仅保留每组配对中的一条，有两种常用实现方案：

方案1：大小比较过滤法（适配你的预期输出） #

直接通过字段字典序判断，保留source小于destination的记录即可，修改最终查询语句：

with A as(
select row_number() over() idx, source from a 
),
B as (
select row_number() over() idx, destination from b
),
C as (
select A.source, B.destination from A join B on A.idx=B.idx
)
select * from C where source < destination;

方案2：通用分组去重法（适配自定义保留规则） #

如果需要按其他规则保留记录（比如固定保留先出现的配对），可以用least和greatest将反向对归为同一组后去重：

with A as(
select row_number() over() idx, source from a 
),
B as (
select row_number() over() idx, destination from b
),
C as (
select A.source, B.destination,
       row_number() over(partition by least(source,destination), greatest(source,destination) order by source) rn
from A join B on A.idx=B.idx
)
select source,destination from C where rn = 1;

两种方案最终输出结果都符合要求：

source  destination 
a           b
c           d

如果你已经生成了实体的C表，想要直接删除冗余记录，执行以下语句即可：

DELETE FROM C WHERE source > destination;

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