如何在SQL中生成“空”聚合结果
我正在尝试优化SQL查询以使我的报告看起来更好。 我的查询从一个表中读取数据，按几个柱分组并计算一些聚合字段（计数和总和）。
SELECT A, B, C, COUNT(*), SUM(D) FROM T
GROUP BY A, B, C
ORDER BY A, B, C
现在，我们假设B和C列是一些定义的常量字符串，例如，B可以是'B1'或'B2' ，C可以是'C1'或'C2' 。 所以，一个示例结果集是：
A | B | C | COUNT(*) | SUM(D)
--------------------------------
A1 | B1 | C1 | 34 | 1752
A1 | B1 | C2 | 4 | 183
A1 | B2 | C1 | 199 | 8926
A1 | B2 | C2 | 56 | 2511
A2 | B1 | C2 | 6 | 89
A2 | B2 | C2 | 12 | 231
A3 | B1 | C1 | 89 | 552
...
正如你所看到的，对于'A1'我有全部四种可能的（B，C）组合，但是'A2'不是这样。 我的问题是：我怎样才能生成（B，C）组合不存在的摘要行，实际上，在给定的表中？ 也就是说，我如何打印这些行，例如：
A | B | C | COUNT(*) | SUM(D)
--------------------------------
A2 | B1 | C1 | 0 | 0
A2 | B2 | C1 | 0 | 0
我能看到的唯一解决方案是用所有（B，C）值创建一些辅助表，然后用该辅助表进行RIGHT OUTER JOIN。 但我正在寻找一种更清洁的方式......
谢谢你们。
辅助表格不一定是真正的表格，它可以是一个通用的表格表达式 - 至少如果你能从表格中获得所有可能的值（或者你感兴趣的所有值）。 使用@Bob Jarvis的查询来生成所有可能的组合，您可以执行如下操作：
WITH CTE AS (
SELECT * FROM (SELECT DISTINCT a FROM T)
JOIN (SELECT DISTINCT b, c FROM T) ON (1 = 1)
)
SELECT CTE.A, CTE.B, CTE.C,
SUM(CASE WHEN T.A IS NULL THEN 0 ELSE 1 END), NVL(SUM(T.D),0)
FROM CTE
LEFT JOIN T ON T.A = CTE.A AND T.B = CTE.B AND T.C = CTE.C
GROUP BY CTE.A, CTE.B, CTE.C
ORDER BY CTE.A, CTE.B, CTE.C;
如果你的固定值可能不在表格中，那么它会更复杂一些（或者无论如何都是丑陋的，并且随着更多可能的值而变得更糟）：
WITH CTE AS (
SELECT * FROM (SELECT DISTINCT a FROM T)
JOIN (SELECT 'B1' AS B FROM DUAL
UNION ALL SELECT 'B2' FROM DUAL) ON (1 = 1)
JOIN (SELECT 'C1' AS C FROM DUAL
UNION ALL SELECT 'C2' FROM DUAL) ON (1 = 1)
)
SELECT CTE.A, CTE.B, CTE.C,
SUM(CASE WHEN T.A IS NULL THEN 0 ELSE 1 END), NVL(SUM(T.D),0)
FROM CTE
LEFT JOIN T ON T.A = CTE.A AND T.B = CTE.B AND T.C = CTE.C
GROUP BY CTE.A, CTE.B, CTE.C
ORDER BY CTE.A, CTE.B, CTE.C;
但是你必须加入一些了解'缺失'价值的东西。 如果其他地方需要相同的逻辑，并且您有固定的值，那么永久性表格可能更清洁 - 当然可能需要维护。 你也可以考虑一个流水线函数作为代理表，但也可能取决于卷。
问题是，如果你的数据库中没有特定的组合，那么引擎如何知道将这个组合包含到结果中？ 为了在结果中包含所有组合，您需要使用所有组合 - 无论是在主表中还是在用于引用的其他表中。 例如，您可以使用如下数据创建另一个表R：
A | B | C
------------
A1 | B1 | C1
A1 | B1 | C2
A1 | B2 | C1
A1 | B2 | C2
A2 | B1 | C1
A2 | B1 | C2
A2 | B2 | C1
A2 | B2 | C2
A3 | B1 | C1
A3 | B1 | C2
A3 | B1 | C1
A3 | B2 | C2
...
然后你的查询将如下所示：
SELECT r.*, COUNT(t.d), coalesce(SUM(t.d), 0)
FROM r LEFT OUTER JOIN t on (r.a=t.a and r.b=t.b and r.c=t.c)
GROUP BY r.a, r.b, r.c
ORDER BY r.a, r.b, r.c
这将以0 | 0返回你想要的集合 0 | 0表示主表中不存在的组合。 请注意，这只有在你知道你想包含的每一种可能的组合的情况下才有可能，但并非总是如此。
另一方面，如果你的A，B，C是数值，你只是想将所有的数字都包含在一个范围内，那么可能有另一种处理方法，例如：
SELECT a.n, b.n, c.n, COUNT(t.d), coalesce(SUM(t.d), 0)
FROM (SELECT (rownum) "n" FROM DUAL WHERE LEVEL >= start_a CONNECT BY LEVEL <= end_a) a,
(SELECT (rownum) "n" FROM DUAL WHERE LEVEL >= start_b CONNECT BY LEVEL <= end_b) b,
(SELECT (rownum) "n" FROM DUAL WHERE LEVEL >= start_c CONNECT BY LEVEL <= end_c) c,
t
WHERE a.n = t.a(+) AND b.n = t.b(+) AND c.n = t.c(+)
GROUP BY a.n, b.n, c.n
ORDER BY a.n, b.n, c.n
（我没有一个方便测试的Oracle实例，所以这更像是一个有点教育的猜测，而不是其他任何事情。）
底线是发动机需要知道什么包含在最终结果中 - 这种或那种方式。
有可能更漂亮的方法来做到这一点，但下面应该让你开始朝着你想要的方向前进：
SELECT * FROM
(SELECT DISTINCT a FROM T)
JOIN
(SELECT DISTINCT b, c FROM T)
ON (1 = 1)
ORDER BY a, b, c
这会给你所有存在B和C的组合，以及所有存在的A，类似于
A1 B1 C1
A1 B1 C2
A1 B2 C1
A1 B2 C2
A2 B1 C1
A2 B1 C2
A2 B2 C1
A2 B2 C2
分享并享受。
链接地址: http://www.djcxy.com/p/10747.html