sql-server - 条件为 COALESCE 的 SQL JOIN 是重复行

Question

我正在尝试将两个表连接在一起。第一个表包含我不想复制的数据记录。我将第二个表加入到第一个表中，以通过不同的 [profileId] 和 [role] 查找 [value]。第二个表中的 [profileId]、[role] 列对组合具有唯一约束，但 [role] 有时可以为 NULL，在这种情况下，我将该值视为该配置文件的默认值。

如何在不复制行且不使用多个左连接的情况下将这些表连接在一起？我的实际查询比示例更复杂。

请参阅下面的示例。

DECLARE @temp TABLE ([profileId] int, [role] int)
DECLARE @temp2 TABLE ([profileId] int, [role] int, [value] nvarchar(50))

INSERT INTO @temp ([profileId], [role]) VALUES (1, 1)
INSERT INTO @temp ([profileId], [role]) VALUES (1, 2)
INSERT INTO @temp ([profileId], [role]) VALUES (2, 1)
INSERT INTO @temp ([profileId], [role]) VALUES (2, 2)
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (1, 1, 'MATCH')
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (1, NULL, 'DEFAULT1')
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (2, NULL, 'DEFAULT2')

SELECT
    T1.[profileId],
    T1.[role],
    T2.value
FROM
    @temp T1
    JOIN @temp2 T2 ON T1.profileId = T2.profileId AND COALESCE(T2.[role], T1.[role]) = T1.[role]

这给了我(我明白为什么)

================================
| profileId | role  |  value   |
================================
|     1     |   1   |  MATCH   |
--------------------------------
|     1     |   1   | DEFAULT1 |
--------------------------------
|     1     |   2   | DEFAULT1 |
--------------------------------
|     2     |   1   | DEFAULT2 |
--------------------------------
|     2     |   2   | DEFAULT2 |
================================

我愿意

================================
| profileId | role  |  value   |
================================
|     1     |   1   |  MATCH   |
--------------------------------
|     1     |   2   | DEFAULT1 |
--------------------------------
|     2     |   1   | DEFAULT2 |
--------------------------------
|     2     |   2   | DEFAULT2 |
================================

Answer 1

这条 SQL 工作正常:

SELECT
    T1.[role],
    Value = coalesce(max(nullif(T2.value,'DEFAULT')),'DEFAULT')
FROM
    @temp T1
    JOIN @temp2 T2 ON COALESCE(T2.[role], T1.[role]) = T1.[role]
group by
    T1.[role]
;