sql-server - 如何加入到 "Other"行

Question

我有两张 table 。一个保存对象，另一个保存每个对象的设置。并非Objects 表中的所有行在Settings 表中都有对应的行。 Settings 表中有一个特殊行，应该用于“其他”对象。

如何在对象和设置之间创建连接，以便在有一个设置时获得给定的设置，或者在没有设置时获得“其他”设置？

例如，考虑以下脚本:

CREATE TABLE #Objects (Code nvarchar(20) not null);
CREATE TABLE #Settings (Code nvarchar(20) not null, Value int not null);

INSERT INTO #Objects
VALUES
    ('A'),
    ('B'),
    ('D')

INSERT INTO #Settings
VALUES
    ('A', 1),
    ('B', 2),
    ('C', 3),
    ('Other', 4)

SELECT 
    #Objects.Code,
    #Settings.Value 
FROM
    #Objects
    JOIN #Settings
        ON #Objects.Code = #Settings.Code
        OR #Settings.Code = 'Other'

DROP TABLE #Settings, #Objects

我想要得到这个:

Code | Value
---- | -----
A    | 1
B    | 2
D    | 4

我实际得到的是:

Code  | Value
----- | -----
A     | 1
A     | 4
B     | 2
B     | 4
D     | 4

Answer 1

您可以使用APPLY来做到这一点:

SELECT o.Code, s.Value
FROM #Objects o
CROSS APPLY (
    SELECT TOP 1 * 
    FROM #Settings s 
    WHERE s.Code = o.Code or s.Code = 'Other' 
    ORDER BY case when s.Code = o.Code then 0 else 1 end
) s

---

为了好玩:来自 Gurv 的答案的混合体, jyao和 SqlZim ，它们都是同一基本主题的变体:

SELECT o.Code, s2.Value
FROM #Objects o
LEFT JOIN #Settings s1 on s1.Code = o.Code
INNER JOIN #Settings s2 on s2.Code = coalesce(s1.Code, 'Other')

到目前为止，这种方法(LEFT JOIN + INNER JOIN ON COALESCE())是我最喜欢的选择。

请注意，这仅在每个对象记录只能有一个设置记录时才有效。如果情况发生变化，应用答案仍然有效，但此处的其他答案可能不起作用。