如何实现带条件优先级的SQL自连接查询？

我需要编写一个自连接查询，优先使用第一个条件进行连接，仅当第一个条件无法匹配到结果时，才使用第二个条件进行连接。

我尝试了以下语法，但无法满足需求——该语句会同时匹配两个条件：

SELECT a.id, a.id2, a.col1, b.id2 from test a
LEFT JOIN test b ON a.id = b.id AND 
(SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1 OR b.col1 = '00')

我需要的是类似XOR的逻辑。

示例数据 #

原表 #

期望结果 #

建表与插入数据SQL #

CREATE TABLE [test](
[id] [int] NOT NULL,
[id2] [int] NOT NULL,
[col1] [varchar](6) NOT NULL
)
INSERT INTO [test] VALUES
(4080,2147,'00'),
(4080,2148,'0001'),
(4080,2149,'0002'),
(4080,2150,'000201'),
(3820,2069,'00'),
(3820,2070,'000101')

解决方案 #

要实现"优先匹配第一个条件，无匹配时才用第二个条件"的逻辑，不能直接用OR（会同时匹配两个条件），可以通过以下两种方式实现：

方法1：两次LEFT JOIN + COALESCE #

先分别用两个LEFT JOIN匹配两个条件，再用COALESCE优先取第一个匹配的结果，同时单独处理col1='00'的情况：

SELECT 
    a.id, 
    a.id2, 
    a.col1, 
    COALESCE(b1.id2, b2.id2) AS b_id2
FROM test a
LEFT JOIN test b1 
    ON a.id = b1.id 
    AND LEN(a.col1) > 2 -- 避免col1长度为2时SUBSTRING报错
    AND SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b1.col1
LEFT JOIN test b2 
    ON a.id = b2.id 
    AND b2.col1 = '00'
    AND b1.id2 IS NULL -- 仅当第一个条件无匹配时才生效
WHERE a.col1 != '00'
-- 单独处理col1为'00'的记录，返回NULL
UNION ALL
SELECT id, id2, col1, NULL AS b_id2
FROM test
WHERE col1 = '00'
ORDER BY id, id2;

方法2：子查询 + CASE判断 #

通过CASE语句先判断第一个条件是否存在匹配，再决定是否使用第二个条件：

SELECT 
    a.id, 
    a.id2, 
    a.col1, 
    CASE 
        WHEN col1 = '00' THEN NULL
        ELSE COALESCE(
            (SELECT TOP 1 b.id2 FROM test b WHERE a.id = b.id AND SUBSTRING(a.col1,1,LEN(a.col1)-2)=b.col1),
            (SELECT TOP 1 b.id2 FROM test b WHERE a.id = b.id AND b.col1='00')
        )
    END AS b_id2
FROM test a
ORDER BY id, id2;

这两种写法都能实现需求：优先匹配SUBSTRING(a.col1,1,LEN(a.col1)-2)=b.col1，只有当该条件无匹配结果时，才匹配b.col1='00'；同时col1='00'的记录返回NULL。

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