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