Group by

Group by 被頻繁的用在分組統計中，最常見的用法：

SQL> select job,deptno,count(*) from scott.emp
  2  where rownum<8
  3  group by  (job,deptno)
  4  order by job;
 
JOB       DEPTNO   COUNT(*)
--------- ------ ----------
CLERK         20          1
MANAGER       10          1
MANAGER       20          1
MANAGER       30          1
SALESMAN      30          3

Group by 還有擴展的幾種用法，靈活使用，可以大大簡化統計過程。

【1】Group by rollup

先上例子

SQL> select job,deptno,count(*) from scott.emp
  2  where rownum<8
  3  group by  rollup(job,deptno)
  4  order by job;
 
JOB       DEPTNO   COUNT(*)
--------- ------ ----------
CLERK         20          1
CLERK                     1
MANAGER       10          1
MANAGER       20          1
MANAGER       30          1
MANAGER                   3
SALESMAN      30          3
SALESMAN                  3
                          7
9 rows selected

仔細觀察可以發現，Group by rollup的結果等同與下面寫法的結果

SQL>  select job,deptno,count(*) from scott.emp
  2   where rownum<8
  3   group by job,deptno
  4   union all
  5   select job,null as deptno,count(*) from scott.emp
  6   where rownum<8
  7   group by job,null
  8   union all
  9   select null,null,count(*) from scott.emp
 10   where rownum<8
 11   order by job,deptno
 12  /
 
JOB           DEPTNO   COUNT(*)
--------- ---------- ----------
CLERK             20          1
CLERK                         1
MANAGER           10          1
MANAGER           20          1
MANAGER           30          1
MANAGER                       3
SALESMAN          30          3
SALESMAN                      3
                              7
9 rows selected

也就是有下面的關係：

group by rollup(job,deptno); n為rollup中分組字段個數
=group by job,deptno
+group by job
+group by null
擴展：
group by rollup(job,deptno),ename; n為rollup中分組字段個數
=group by job,deptno,ename
+group by job,ename
+group by null,ename

【2】Group by cube

先上例子：

SQL>   select job,deptno,count(*) from scott.emp
  2    where rownum<8
  3    group by cube(job,deptno)
  4    order by job;
 
JOB       DEPTNO   COUNT(*)
--------- ------ ----------
CLERK         20          1
CLERK                     1
MANAGER       10          1
MANAGER       20          1
MANAGER       30          1
MANAGER                   3
SALESMAN      30          3
SALESMAN                  3
              10          1
              20          2
              30          4
                          7
12 rows selected

結果等同於下面寫法的結果：

SQL> select job,deptno,count(*) from scott.emp
  2  where rownum<8
  3  group by (job,deptno)
  4  union all
  5  select job,null deptno,count(*) from scott.emp
  6  where rownum<8
  7  group by (job)
  8  union all
  9  select null,deptno,count(*) from scott.emp
 10  where rownum<8
 11  group by (deptno)
 12  union all
 13  select null,null,count(*) from scott.emp
 14  where rownum<8
 15  /
 
JOB           DEPTNO   COUNT(*)
--------- ---------- ----------
MANAGER           20          1
SALESMAN          30          3
MANAGER           30          1
MANAGER           10          1
CLERK             20          1
CLERK                         1
SALESMAN                      3
MANAGER                       3
                  30          4
                  20          2
                  10          1
                              7
12 rows selected

也就是：

group by cube(job,deptno)  組合類型有2^n種，n為cube中分組字段個數
=group by (job,deptno)
+group by (job)
+group by (deptno)
+group by (null)
擴展：
group by cube(job,deptno),ename  組合類型有2^n種，n為cube中分組字段個數
=group by (job,deptno,ename)
+group by (job,ename)
+group by (deptno,ename)
+group by (null,ename)

【3】Group by  grouping sets

同樣先上例子

SQL> select job,deptno,count(*) from scott.emp
  2  where rownum<8
  3  group by grouping sets(job,deptno)
  4  order by job;
 
JOB       DEPTNO   COUNT(*)
--------- ------ ----------
CLERK                     1
MANAGER                   3
SALESMAN                  3
              10          1
              30          4
              20          2
6 rows selected

其結果是分別按grouping sets中字段單獨分組

SQL> select job,null,count(*) from scott.emp
  2  where rownum<8
  3  group by job
  4  union all
  5  select null,deptno,count(*) from scott.emp
  6  where rownum<8
  7  group by deptno
  8  order by job;
 
JOB             NULL   COUNT(*)
--------- ---------- ----------
CLERK                         1
MANAGER                       3
SALESMAN                      3
                  10          1
                  30          4
                  20          2
6 rows selected

也就是：

group by grouping sets(job,deptno) n中組合類型，n為sgrouping sets中字段的個數
=group by job
+group by deptno
擴展：
group by grouping sets(job,deptno),ename 
=group by job,ename
+group by deptno,ename