DB2 9.0 GROUPING SETS & ROLLUP & CUBE 比较

最新推荐文章于 2021-09-16 13:13:52 发布

转载最新推荐文章于 2021-09-16 13:13:52 发布 · 1.2k 阅读

文章标签：

#db2 #c #nested

Oracle 同时被 2 个专栏收录

35 篇文章

订阅专栏

047相关细节

5 篇文章

订阅专栏

本文详细解释了 DB2 9.0 中的 GROUPINGSETS、ROLLUP 和 CUBE 的用法及区别，通过实例展示了它们如何用于数据分组和汇总。

DB2 9.0 GROUPING SETS & ROLLUP & CUBE 比较

GROUPING SETS

-------------------------------------------------------------------------

GROUP BY GROUPING SETS((),())--(NESTED FLAG,GROUP BY UNION ALL)

GROUPING SETS((A,B,C))

=GROUP BY A,B,C

GROUPING SETS(A,(B,C))

=GROUP BY A UNION ALL GROUP BY B,C

GROUPING SETS(A,B,C)

=GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C
-------------------------------------------------------------------------

GROUP BY GROUPING SETS(),GROUPING SETS() = GROUP BY (),()
GROUPING SETS(A),GROUPING SETS(B),GROUPING SETS(C)

=GROUP BY A,B,C

GROUPING SETS(A),GROUPING SETS((B,C))

=GROUP BY A,B,C

GROUPING SETS(A),GROUPING SETS(B,C)

=GROUP BY A,B UNION ALL GROUP BY A,C
-------------------------------------------------------------------------
GROUP BY (),GROUPING SETS() = GROUP BY (),()

GROUP BY A,B,GROUPING SETS((B,C))

= GROUP BY A,B,B,C
= GROUP BY A,B,C

GROUP BY A,B,GROUPING SETS(B,C)

= GROUP BY A,B,B UNION ALL GROUP BY A,B,C
= GROUP BY A,B UNION ALL GROUP BY A,B,C

-------------------------------------------------------------------------

ROLLUP
-------------------------------------------------------------------------

GROUP BY ROLLUP(A,B,C)

=GROUPING SETS((A,B,C),(A,B),(A),())

GROUP BY ROLLUP(A,(B,C))

=GROUPING SETS((A,B,C),(A),())

GROUP BY ROLLUP(A),ROLLUP(B,C)

=GROUPING SETS(A,())*GROUPING SETS((B,C),(B),())
=GROUPING SETS((A,B,C),(A,B),(A),(B,C),(B),())

-------------------------------------------------------------------------

CUBE
-------------------------------------------------------------------------
GROUP BY CUBE(A,B,C)

=GROUPING SETS((A,B,C),(A,B),(A,C),(A),(B,C),(B),(C),())

GROUP BY CUBE(A,(B,C))

=GROUPING SETS((A,B,C),(A),(B,C),())

GROUP BY CUBE(A,B),CUBE(B,C)

=GROUPING SETS((A,B),(A),(B),())*GROUPING SETS((B,C),(B),(C),())
=GROUPING SETS(

                                                               (A,B,B,C),(A,B,B),(A,B,C),(A,B),
                                                                (A,B,C),(A,B),(A,C),(A),
                                                                (B,B,C),(B,B),(B,C),(B),
                                                                (B,C),(B),(C),())

                 =GROUPING SETS(
                                                                (A,B,C),(A,B),(A,B,C),(A,B),
                                                                (A,B,C),(A,B),(A,C),(A),
                                                                (B,C),(B),(B,C),(B),
                                                                (B,C),(B),(C),())