DB2多种GROUPBY的使⽤平常经常使⽤GROUP BY对数据进⾏分组运算，但是还有其他的分组运⾏情况。
GROUP BY CUBE()、GROUP BY  ROLLUP()、GROUP BY GROUPING SETS()
以原始数据表数据为例：
【1】GROUP BY CUBE()的分组情况
结果集相当于
SELECT SUM(SALARY) FROM TEST_SALARY  @groupby分组
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个组合

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。