sql - 需要一个 SQL 查询来填写值，以便每个缺失的选项都存在于结果集中

Question

我有一个只有原始数据的表：一列中的联系人，以及他们在另一列中选择的选项，如下所示：

Contact Option
A        1
A        3
B        1
C        2
D        3
E        2
E        3

TableOfOptions
1
2
3
4

我想要的是返回一个结果集，其中每个选项都有一个联系人，无论它是否被选中。基本上，我想“填写”结果集，未选择的选项为 NULL。所以有两张表，一张是联系人到选项选择的映射，一张是选项列表。所需的结果集如下所示。

Contact Option Chosen
A       1       1
A       2       NULL
A       3       3
A       4       NULL
B       1       1
B       2       NULL
B       3       NULL
B       4       NULL
C       1       NULL
C       2       2
C       3       NULL
C       4       NULL

等等等等，我相信你明白了。我不知道如何为“ContactOptions”表中的每个联系人投影“选项”表中的每一行，为 OptionChosen 填充缺失的行。每个“联系人”都会有至少一个选项可供选择，有的可能有几个，有的可能有全部。所以我需要在每个联系人行中“添加”0 到“n-1”选项，所有“添加”的行都是 NULL。

我的第一个虽然是 CROSS JOIN 并且有一个 CASE WHEN x.Option = y.Option 然后 x.Option else NULL END 用于第三列......但我发现在某些情况下我得到重复的行，并且我不知道为什么。

请注意，我的真实示例稍微复杂一些，因为我想携带更多的属性，而不仅仅是一个（即，联系人 A 有四个其他属性，我想在每个选项中重复，但我认为这与解决方案没有太大关系）。但是“Contact + Option”的组合在结果集中应该是唯一的。

我的最终目标是能够查询给定选项，但始终获得所有联系人。例如，如果我想查看“人们是如何选择选项 2”的，我希望 A 到 E 在结果集中各有一行，根据他们是否选择该选项，有一个值或一个 NULL。

编辑：这是我尝试过的示例代码，但这会导致我不想要很多重复的行：

CREATE TABLE ContactOption ([Contact] CHAR(1), [Option] int);
INSERT INTO ContactOption ([Contact], [Option])
VALUES
('A', 1),
('A', 3),
('B', 1),
('C', 2),
('D', 3),
('E', 2),
('E', 3);

CREATE TABLE TableOfOptions ([Option] int);
INSERT INTO TableOfOptions ([Option])
VALUES
(1),
(2),
(3),
(4);

SELECT co.[Contact],
       t.[Option],
       CASE WHEN co.[Option] = t.[Option] THEN co.[Option] ELSE NULL END AS [Choice]
FROM ContactOption co
     CROSS JOIN TableOfOptions t
ORDER BY co.[Contact], t.[Option]

标签： sqlsql-servertsql

Answer 1

如果没有具有不同值的表，Contact您将需要至少 1DISTINCT来获取这些DISTINCT值：

WITH Contacts AS(
    SELECT DISTINCT CO.Contact
    FROM dbo.ContactOption CO)
SELECT C.Contact,
       TOO.[Option],
       CO.[Option]
FROM Contacts C
     CROSS JOIN dbo.TableOfOptions TOO
     LEFT JOIN dbo.ContactOption CO ON C.Contact = CO.Contact
                                   AND TOO.[Option] = CO.[Option];