DB2 多种GROUP BY的使用

平常经常使用GROUP BY对数据进行分组运算,但是还有其他的分组运行情况。

GROUP BY CUBE()、GROUP BY  ROLLUP()、GROUP BY GROUPING SETS()

以原始数据表数据为例:

【1】GROUP BY CUBE()的分组情况

结果集相当于

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME, SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT DEPART, SUM(SALARY) FROM TEST_SALARY GROUP BY DEPART@

SELECT NAME,DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME,DEPART@

这四个结果集合集。

【2】GROUP BY ROLLUP()

结果集相当于 

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT NAME,DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME,DEPART@

这三种结果集的合集。

【3】GROUPING SETS()

结果集相当于 

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY DEPART@

这三种结果集的合集。

 

总结:

【2】group by cube(A,B) = all + group by A + group by B + group by A,B
         group by cube(A,B,C) = all + group by A + group by B + group by C + group by A,B + group by A,C + group by B,C    + group by A,B,C
        ==> group by cube 的结果是2的n次幂个组合
      

【3】group by rollup(A,B) = all + group by A + group by A,B
         group by rollup(A,B,C) = all + group by A + group by A,B + group by A,B,C
        ==> group by rollup 的结果是n+1个组合
        
 【4】group by grouping sets(A,B,()) = group by A + group by B + all
         group by grouping sets(A,B,C,()) = group by A + group by B + group by C +all
        ==> group by grouping sets 的结果是 n个组合

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值