ORACLE GROUPING函數的使用

GROUPING函數可以接受一列，返回0或者1。如果列值為空白，那麼GROUPING()返回1；如果列值非空，那麼返回0。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。當需要在返回空值的地方顯示某個值時，GROUPING()就非常有用。

1、在ROLLUP中對單列使用GROUPING()

SQL> select division_id,sum(salary)2 from employees23 group by rollup(division_id)4 order by division_id;DIV SUM(SALARY)--- -----------BUS 1610000OPE 1320000SAL 4936000SUP 10150008881000

加上GROUPING來看看

SQL> select grouping(division_id),division_id,sum(salary)2 from employees23 group by rollup(division_id)4 order by division_id;GROUPING(DIVISION_ID) DIV SUM(SALARY)--------------------- --- -----------0 BUS 16100000 OPE 13200000 SAL 49360000 SUP 10150001 8881000

可以看到，為空白的地方返回1，非空的地方返回0。

2、使用CASE轉換GROUPING()的傳回值
可能你會覺得前面的0和1太枯燥了，代表不了任何意義，說白了就是不夠人性化，呵呵。這個時候我們可以使用CASE來轉換為一些有意義的值。

SQL> select2 case grouping(division_id)3 when 1 then 'all divisions'4 else division_id5 end as div,6 sum(salary)7 from employees28 group by rollup(division_id)9 order by division_id;DIV SUM(SALARY)------------- -----------BUS 1610000OPE 1320000SAL 4936000SUP 1015000all divisions 8881000

3、使用CASE和GROUPING()轉換多個列的值

SQL> select2 case grouping(division_id)3 when 1 then 'all divisions'4 else division_id5 end as div,6 case grouping(job_id)7 when 1 then 'all jobs'8 else job_id9 end as job,10 sum(salary)11 from employees212 group by rollup(division_id,job_id)13 order by division_id,job_id;DIV JOB SUM(SALARY)------------- -------- -----------BUS MGR 530000BUS PRE 800000BUS WOR 280000BUS all jobs 1610000OPE ENG 245000OPE MGR 805000OPE WOR 270000OPE all jobs 1320000SAL MGR 4446000SAL WOR 490000SAL all jobs 4936000DIV JOB SUM(SALARY)------------- -------- -----------SUP MGR 465000SUP TEC 115000SUP WOR 435000SUP all jobs 1015000all divisions all jobs 888100016 rows selected.

 

4、CUBE與GROUPING()結合使用

SQL> select2 case grouping(division_id)3 when 1 then 'all divisions'4 else division_id5 end as div,6 case grouping(job_id)7 when 1 then 'all jobs'8 else job_id9 end as job,10 sum(salary)11 from employees212 group by cube(division_id,job_id)13 order by division_id,job_id;DIV JOB SUM(SALARY)------------- -------- -----------BUS MGR 530000BUS PRE 800000BUS WOR 280000BUS all jobs 1610000OPE ENG 245000OPE MGR 805000OPE WOR 270000OPE all jobs 1320000SAL MGR 4446000SAL WOR 490000SAL all jobs 4936000DIV JOB SUM(SALARY)------------- -------- -----------SUP MGR 465000SUP TEC 115000SUP WOR 435000SUP all jobs 1015000all divisions ENG 245000all divisions MGR 6246000all divisions PRE 800000all divisions TEC 115000all divisions WOR 1475000all divisions all jobs 888100021 rows selected.

 

5、使用GROUPING SETS子句
使用GROUPING SETS子句可以只返回小計記錄。

SQL> select division_id,job_id,sum(salary)2 from employees23 group by grouping sets(division_id,job_id)4 order by division_id,job_id;DIV JOB SUM(SALARY)--- --- -----------BUS 1610000OPE 1320000SAL 4936000SUP 1015000ENG 245000MGR 6246000PRE 800000TEC 115000WOR 14750009 rows selected.