sql - 将不存在的数据添加到 SQL 查询

Question

我的 SQL 查询返回以下结果 ( screenshot ):

x           y           count
----------- ----------- -----------
1           1           10
1           2           2
2           4           3
2           5           5
4           1           5
5           1           8

我想要的是 x, y 应该总是包含 1 到 5 个值，即使查询没有返回它们，在上面的场景中 x 是缺失的 3. 如何在这里添加介于 1 和 5 之间的缺失值.

提前致谢

Answer 1

首先您需要生成所需的数据。您可以为此使用数字表。使用 CROSS JOIN 生成两个表的所有可能组合。最后，OUTER JOIN 生成的数据与您的表。

在下面的查询中，我使用 union 构建了一个数字列表，而不是从表中获取它们。但想法保持不变:

SELECT XList.x, YList.y, #temp.count
FROM (
    SELECT 1 AS x UNION ALL
    SELECT 2      UNION ALL
    SELECT 3      UNION ALL
    SELECT 4      UNION ALL
    SELECT 5
) AS XList 
CROSS JOIN (
    SELECT 1 AS y UNION ALL
    SELECT 2      UNION ALL
    SELECT 3      UNION ALL
    SELECT 4      UNION ALL
    SELECT 5
) AS YList
LEFT JOIN #temp ON XList.x = #temp.x AND YList.y = #temp.y

结果:

x           y           count
----------- ----------- -----------
1           1           10
2           1           NULL
3           1           NULL
4           1           5
5           1           8
1           2           2
2           2           NULL
3           2           NULL
4           2           NULL
5           2           NULL
1           3           NULL
2           3           NULL
3           3           NULL
4           3           NULL
5           3           NULL
1           4           NULL
2           4           3
3           4           NULL
4           4           NULL
5           4           NULL
1           5           NULL
2           5           5
3           5           NULL
4           5           NULL
5           5           NULL