2021年经历万千,有好有坏,也好久没有在这儿写过什么了,就在2021的最后几天里写点东西,给2021年留点什么吧。:-)
分组汇总求和是在常规的打印,数据统计中应用很多的一种方式。
With rollup,With cube(也可以写成 rollup(),cube())与Grouping sets在进来数据分组汇总中效率会比较高,且可以大大减少Sql的角本量
--原始数据如下
SELECT id,b,a FROM (
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
构建表如下:
--Rollup 方式
SELECT ID,B,SUM(A) F FROM
(
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
GROUP BY id,b with rollup
或
SELECT ID,B,SUM(A) F FROM
(
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
GROUP BY rollup( id,b)
效果一样
效果如下:
--Cube 方式
SELECT ID,B,SUM(A) F FROM
(
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
GROUP BY id,b with cube
或
SELECT ID,B,SUM(A) F FROM
(
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
GROUP BY cube ( id,b )
效果一下
效果如下:
--Grouping sets 方式
SELECT ID,B,SUM(A) F FROM
(
select 'A1' ID,1 A,'A' B UNION
select 'A2' ID,2 A,'B' B UNION
select 'A3' ID,3 A,'C' B UNION
select 'A4' ID,4 A,'D' B UNION
select 'A5' ID,5 A,'B' B UNION
select 'A6' ID,6 A,'C' B UNION
select 'A7' ID,7 A,'D' B UNION
select 'A8' ID,8 A,'A' B UNION
select 'A9' ID,9 A,'C' B )X
GROUP BY grouping sets((id,b),(id),(b),())
注:
sets 里的组合不同,效果将不同,这是与以上两种方式最大的区别且也是最大的优势
效果如下: