我正在尝试优化SQL查询以使我的报告看起来更好.我的查询从一个表读取数据,按几个列分组,并计算一些聚合字段(计数和总和).
SELECT A,B,C,COUNT(*),SUM(D) FROM T GROUP BY A,C ORDER BY A,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)值的辅助表,然后使用该aux表进行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.C
ORDER BY CTE.A,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.C;
但你必须加入一些了解“缺失”价值观的东西.如果在其他地方需要相同的逻辑,并且您有固定的值,那么永久表可能更清晰 – 当然,可能需要维护.您还可以将流水线函数视为代理表,但可能依赖于卷.