SQL删除自连接后不同列含相同键的对称重复行方案咨询

核心问题原因 #

自连接使用sub.SUB_BRAND_ID != sub2.SUB_BRAND_ID作为判断条件时，会同时匹配(X123,Y123)和(Y123,X123)两组结果，两行字段值互换后不属于完全相同的行，因此DISTINCT无法识别去重。

方案1：修改WHERE判断条件（优先推荐，性能最优） #

将不等条件替换为大小比较，强制仅保留ID1小于ID2的匹配结果，每组互逆ID对仅返回1条记录，无需额外计算开销，修改后SQL如下：

select
brands.BRAND_ID as Brand_Code,
sub.SUB_BRAND_ID as Sub_Brand_ID1,
sub.SUB_BRAND as Sub_Brand_Description1,
sub2.SUB_BRAND_ID as Sub_Brand_ID2,
sub2.SUB_BRAND as Sub_Brand_Description2

from table1 as brands
inner join table2 as sub on sub.BRAND_ID = brands.BRAND_ID and sub.LANGU = 'E'
inner join table2 as sub2 on sub2.SUB_BRAND = sub.SUB_BRAND and sub2.LANGU = 'E'

where sub.SUB_BRAND_ID < sub2.SUB_BRAND_ID and sub.BRAND_ID = sub2.BRAND_ID

注：修改后无需再使用DISTINCT，已经不会产生重复结果。

方案2：窗口函数去重（兼容特殊场景） #

如果ID规则不支持直接大小比较，可以用LEAST、GREATEST函数对无序ID对分组后去重：

with base_result as (
select
brands.BRAND_ID as Brand_Code,
sub.SUB_BRAND_ID as Sub_Brand_ID1,
sub.SUB_BRAND as Sub_Brand_Description1,
sub2.SUB_BRAND_ID as Sub_Brand_ID2,
sub2.SUB_BRAND as Sub_Brand_Description2,
row_number() over(
    partition by brands.BRAND_ID, least(sub.SUB_BRAND_ID, sub2.SUB_BRAND_ID), greatest(sub.SUB_BRAND_ID, sub2.SUB_BRAND_ID)
    order by sub.SUB_BRAND_ID
) as rn
from table1 as brands
inner join table2 as sub on sub.BRAND_ID = brands.BRAND_ID and sub.LANGU = 'E'
inner join table2 as sub2 on sub2.SUB_BRAND = sub.SUB_BRAND and sub2.LANGU = 'E'
where sub.SUB_BRAND_ID != sub2.SUB_BRAND_ID and sub.BRAND_ID = sub2.BRAND_ID
)
select Brand_Code, Sub_Brand_ID1, Sub_Brand_Description1, Sub_Brand_ID2, Sub_Brand_Description2
from base_result where rn = 1

原理：LEAST取两个ID中的较小值，GREATEST取较大值，同一对ID无论顺序如何，这两个函数的返回结果都相同，以此为分组依据标记行号，取每组第一条即可实现去重。 #

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