T-SQL多表连接出现多行重复结果的问题求助

我现在遇到一个T-SQL的连接问题，写了一段代码后得到的结果不符合预期，想请大家帮忙看看哪里出问题了。

先贴一下我的代码：

DECLARE @instance TABLE
(
instance_name VARCHAR(40),
b VARCHAR(40),
c VARCHAR(40)
)

INSERT INTO @instance
VALUES
('inst1', 'a', 'a'),
('inst2', 'a', 'b'),
('inst3', 'a', 'c')

DECLARE @db TABLE
(
[database_name] VARCHAR(40),
instance_name VARCHAR(40),
f VARCHAR(40)
)

INSERT INTO @db
VALUES
('db1', 'inst1', 'a'),
('db2', 'inst1', 'b')

DECLARE @override TABLE
(
name VARCHAR(40),
instance_name VARCHAR(40),
[database_name] VARCHAR(40),
i VARCHAR(40)
)

INSERT INTO @override
VALUES
('inst1.db1', 'inst1', 'db1', 'a'),
('inst1', 'inst1', NULL, 'b')

SELECT
i.instance_name, d.database_name, s.i
FROM
@instance i
JOIN
@db d ON i.instance_name = d.instance_name
FULL OUTER JOIN
@override s ON d.instance_name + '.' + d.database_name = s.name
OR (d.instance_name = s.name AND s.database_name IS NULL)

现在得到的输出是：

但我期望inst1.db1对应的Value应该只有a，因为数据库级的覆盖（inst1.db1）应该优先于实例级的覆盖（inst1），结果现在两行都出来了，我觉得是连接条件的问题，有没有大佬能指点一下？

问题分析 #

你遇到的核心问题是FULL OUTER JOIN的条件同时匹配了两条override记录：
对于inst1.db1这条数据库记录，它既匹配了override表中name='inst1.db1'的行（数据库级覆盖），又匹配了name='inst1'且database_name IS NULL的行（实例级覆盖），所以连接后直接产生了两行重复结果。

解决方案 #

根据你的需求（数据库级覆盖优先于实例级），可以用以下几种方式处理：

方案1：用LEFT JOIN + COALESCE优先取数据库级覆盖

先尝试匹配数据库级的override，匹配不到再取实例级的，这样就不会出现重复行，逻辑也很直观：

SELECT
    i.instance_name, 
    d.database_name, 
    COALESCE(s_db.i, s_inst.i) AS i
FROM
    @instance i
JOIN
    @db d ON i.instance_name = d.instance_name
LEFT JOIN
    @override s_db ON d.instance_name + '.' + d.database_name = s_db.name
LEFT JOIN
    @override s_inst ON d.instance_name = s_inst.name AND s_inst.database_name IS NULL

方案2：用ROW_NUMBER()筛选优先级最高的覆盖记录

如果后续override表可能新增更多层级的覆盖规则，这个方案扩展性更好——给每条匹配的override记录标记优先级，然后只取优先级最高的那一行：

WITH joined_data AS (
    SELECT
        i.instance_name, 
        d.database_name, 
        s.i,
        -- 数据库级覆盖优先级设为1，实例级为2，数字越小优先级越高
        ROW_NUMBER() OVER (PARTITION BY i.instance_name, d.database_name ORDER BY CASE WHEN s.database_name IS NOT NULL THEN 1 ELSE 2 END) AS rn
    FROM
        @instance i
    JOIN
        @db d ON i.instance_name = d.instance_name
    LEFT JOIN
        @override s ON d.instance_name + '.' + d.database_name = s.name
        OR (d.instance_name = s.name AND s.database_name IS NULL)
)
SELECT instance_name, database_name, i
FROM joined_data
WHERE rn = 1

你可以根据自己的实际场景选择合适的方案，比如如果只有这两级覆盖，方案1最简单；如果以后可能有更多层级的规则，方案2会更灵活。

备注：内容来源于stack exchange，提问作者john