如何编写多条件JOIN两表的SQL，按匹配优先级返回无重复结果

算力阿峰

2026-10-1

我刚接触复杂SQL查询（若该需求不算复杂可直接告知），目前需要关联两张表实现多种匹配组合，以下是我实现第一种组合的查询：

SELECT DISTINCT
    A.Id, A.Ref1, A.Ref2, A.Ref3, B.Ref1, B.Ref2, B.Ref3
FROM
    A
JOIN
    B ON TRIM(UPPER(B.Ref1)) = TRIM(UPPER(A.Ref1)) 
      AND TRIM(UPPER(B.Ref2)) = TRIM(UPPER(A.Ref2)) 
      AND TRIM(UPPER(B.Ref3)) = TRIM(UPPER(A.Ref3))

Ref1到Ref3是第一种组合的必要匹配字段，该场景归类为 Exact Match（精确匹配）。
若仅两个字段匹配，则归类为 Partial Match 1（部分匹配1），仅单个字段匹配归类为 Partial Match 2（部分匹配2），所有可能的匹配组合如下：

A.Ref1 = B.Ref1 且 A.Ref2 = B.Ref2 且 A.Ref3 = B.Ref3：Exact Match
A.Ref1 = B.Ref1 且 A.Ref2 = B.Ref2：Partial Match 1
A.Ref2 = B.Ref2 且 A.Ref3 = B.Ref3：Partial Match 1
A.Ref1 = B.Ref1 且 A.Ref3 = B.Ref3：Partial Match 1
A.Ref1 = B.Ref1：Partial Match 2
A.Ref2 = B.Ref2：Partial Match 2
A.Ref3 = B.Ref3：Partial Match 2

我的核心需求是：已经命中Exact Match的记录不出现在Partial Match 1结果中，同时命中Exact Match或Partial Match 1的记录都不出现在Partial Match 2结果中。
我之前的写法初期运行正常，但加入会导致匹配结果不唯一的值后就出现问题，已经命中Exact Match的记录还会出现在Partial Match结果中，产生冗余，示例结果如下：

Id     A.Ref1     A.Ref2     A.Ref3     B.Ref1     B.Ref2     B.Ref3     Match Type
 1     Val1       Val1       Val1       Val1       Val1       Val1       Exact Match
 1     Val1       Val1       NULL       Val1       Val1       NULL       Partial Match 1
 1     Val1       NULL       Val1       Val1       NULL       Val1       Partial Match 1
 1     Val1       NULL       NULL       Val1       NULL       NULL       Partial Match 2

上述场景中ID1已经是Exact Match，我不希望它重复出现在Partial Match结果中，除非存在其他不同的字段值。
我希望可以用单条查询实现该功能，拆分多条也可接受，若支持单条实现更好，因为该查询会被频繁执行。

更新

参考@Thorsten Kettner的回答，我用WITH子句实现了符合需求的查询，代码如下。单字段匹配的Partial_Matches2存在少量不稳定问题，但目前无紧急修复需求，整体结果已经符合预期。

-- 提取精确匹配结果
with exact_matches as 
(
    Select Distinct
            Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3, 'Exact Match' as "Match_Type"
    from  A
    Join B
        On  TRIM(UPPER(B.Ref1)) = TRIM(UPPER(A.Ref1)) And 
            TRIM(UPPER(B.Ref2)) = TRIM(UPPER(A.Ref2)) And
            TRIM(UPPER(B.Ref3)) = TRIM(UPPER(A.Ref3))
),
-- 提取双字段匹配的部分匹配1结果，排除已命中精确匹配的记录
partial_matches1 as (
    Select 
        distinct A.*, 
        Case 
            When A.Ref1 <> A.Ref1 Or A.Ref1 is NULL
                Then 'No match by Ref 1'
            When A.Ref2 <> A.Ref2 Or A.Ref2 is NULL
                Then 'No match by Ref 2'
            When A.Ref3 <> A.Ref3 Or A.Ref3 is NULL
                Then 'No match by Ref 3'
        End as "Match_Type"
    from (
                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref1)) = TRIM(UPPER(A.Ref1)) And 
                        TRIM(UPPER(B.Ref2)) = TRIM(UPPER(A.Ref2)) 

                Union

                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref2)) = TRIM(UPPER(A.Ref2)) And 
                        TRIM(UPPER(B.Ref3)) = TRIM(UPPER(A.Ref3))

                Union

                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref1)) = TRIM(UPPER(A.Ref1)) And
                        TRIM(UPPER(B.Ref3)) = TRIM(UPPER(A.Ref3))
    ) As A
        Left Join exact_matches As B
            On B.Id = A.Id
        Where B.Id is NULL
),
-- 提取单字段匹配的部分匹配2结果，排除已命中精确匹配和部分匹配1的记录
partial_matches2 as
(
    Select 
        distinct A.*, 

        Case 
            When A.Ref1 = A.Ref1
                Then 'Match by Ref1'
            When A.Ref2 = A.Ref2
                Then 'Match by Ref2'
            When A.Ref3 = A.Ref3
                Then 'Match by Ref3'
        End as "Match_Type"
    from (
                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref1)) = TRIM(UPPER(A.Ref1))

                Union

                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref2)) = TRIM(UPPER(A.Ref2))

                Union

                Select Distinct
                        Id, A.Ref1, B.Ref1, A.Ref2, B.Ref2, A.Ref3, B.Ref3
                from A
                Join B
                    On  TRIM(UPPER(B.Ref3)) = TRIM(UPPER(A.Ref3))
    ) As A
        Left Join exact_matches As B
            On B.Id = A.Id
        Left Join partial_matches1 As C
            On C.Id = A.Id
        Where B.Id is NULL and C.Id is NULL

)

-- 主查询，整合所有匹配结果
select 
    Distinct
    A.Id, B.Ref1, B.Ref2, B.Ref3,
    (
        Case
            When B.Match_Type Is Not NULL
                Then B.Match_Type
            Else
                'No Match'
        End
    ) As 'Match_Type'
    from  A

        /* 左连接关联所有匹配结果 */
        Left Join (
            select * from exact_matches
            Union
            select * from partial_matches1
            Union
            select * from partial_matches2
        ) B
            On B.Id = A.Id

Order By A.Id

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

如何编写多条件JOIN两表的SQL，按匹配优先级返回无重复结果

更新 #

更新