如何去除SQL查询结果中互为反向重复的行？

执行以下SQL语句后，查询结果返回两条内容互为反向的重复记录，需要修改语句以仅获取一条记录：

SELECT
    a.SID, a.SID_SCH, a.EXCLUDED_SID, a.EXCLUDED_SID_SCH, 
    c.DESC, d.DESC EXCLUDED_DESC 
FROM
    EXCLUSIONS A
INNER JOIN
    ITEMS B ON a.SID = b.SID AND a.SID_sch = b.SID_sch
INNER JOIN
    code c ON a.SID = c.SID AND a.SID_sch = c.SID_sch
INNER JOIN
    code d ON a.EXCLUDED_SID = d.SID AND a.EXCLUDED_SID_SCH = d.SID_sch
WHERE 
    b.ITEM_ID IN ('D5FAF478-CF43-40E1-BE79-BB90147A3194',
                  '2E79B23E-D264-4901-A065-7E0B7032A5D8')

当前输出结果：

072 A   010 A   Create    Create1
010 A   072 A   Create1   Create

修改方案 #

在WHERE子句中添加排序条件，确保只保留其中一组有序的记录：

SELECT
    a.SID, a.SID_SCH, a.EXCLUDED_SID, a.EXCLUDED_SID_SCH, 
    c.DESC, d.DESC EXCLUDED_DESC 
FROM
    EXCLUSIONS A
INNER JOIN
    ITEMS B ON a.SID = b.SID AND a.SID_sch = b.SID_sch
INNER JOIN
    code c ON a.SID = c.SID AND a.SID_sch = c.SID_sch
INNER JOIN
    code d ON a.EXCLUDED_SID = d.SID AND a.EXCLUDED_SID_SCH = d.SID_sch
WHERE 
    b.ITEM_ID IN ('D5FAF478-CF43-40E1-BE79-BB90147A3194',
                  '2E79B23E-D264-4901-A065-7E0B7032A5D8')
    AND (a.SID, a.SID_SCH) < (a.EXCLUDED_SID, a.EXCLUDED_SID_SCH)

说明 #

通过比较(a.SID, a.SID_SCH)和(a.EXCLUDED_SID, a.EXCLUDED_SID_SCH)的组合值，利用数据库排序规则，只会保留符合“正向”顺序的记录，过滤掉反向重复行。如果数据库不支持多列比较语法，可拆分为单独条件：

AND (a.SID < a.EXCLUDED_SID OR (a.SID = a.EXCLUDED_SID AND a.SID_SCH < a.EXCLUDED_SID_SCH))

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