SQL查询统计

最新推荐文章于 2024-08-28 03:57:03 发布

weixin_30745641

最新推荐文章于 2024-08-28 03:57:03 发布

阅读量51

点赞数

原文链接：http://www.cnblogs.com/mingdep/archive/2013/01/12/2857759.html

版权

SQL> select a, b, sum(c), grouping(a), grouping(b), grouping(c) from test group by rollup(a, b, c);

A       B             SUM(C) GROUPING(A) GROUPING(B) GROUPING(C)
---------- ---------- ---------- ----------- ----------- -----------
a       aaa             10          0          0          0
a       aaa             20          0          0          0
a       aaa             30          0          0          1
a       bbb             30          0          0          0
a       bbb             40          0          0          0
a       bbb             70          0          0          1
a                         100          0          1          1
aa       ccc             50          0          0          0
aa       ccc             60          0          0          0
aa       ccc             110          0          0          1
aa       ddd             70          0          0          0
aa       ddd             70          0          0          1
aa                         180          0          1          1
                           280          1          1          1

14 rows selected

SQL> select a, b, sum(c), grouping(a), grouping(b), grouping(c) from test group by rollup(a, b, c) having grouping(c) = 1;

A       B             SUM(C) GROUPING(A) GROUPING(B) GROUPING(C)
---------- ---------- ---------- ----------- ----------- -----------
a       aaa             30          0          0          1
a       bbb             70          0          0          1
a                         100          0          1          1
aa       ccc             110          0          0          1
aa       ddd             70          0          0          1
aa                         180          0          1          1
                           280          1          1          1

7 rows selected

SQL> select a, b, sum(c), grouping(a), grouping(b) from test group by rollup(a, b);

A       B             SUM(C) GROUPING(A) GROUPING(B)
---------- ---------- ---------- ----------- -----------
a       aaa             30          0          0
a       bbb             70          0          0
a                         100          0          1
aa       ccc             110          0          0
aa       ddd             70          0          0
aa                         180          0          1
                           280          1          1

7 rows selected

SQL>