函数---GROUPING

GROUPINGGROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。1、在ROLLUP中对单列使用GROUPING()SQL> select division_id,sum(salary)  2  from employees2  3  group by rollup(division_id)  4  order by division_id;DIV SUM(SALARY)--- -----------BUS     1610000OPE     1320000SAL     4936000SUP     1015000        8881000加上GROUPING来看看SQL> select grouping(division_id),division_id,sum(salary)  2  from employees2  3  group by rollup(division_id)  4  order by division_id;GROUPING(DIVISION_ID) DIV SUM(SALARY)--------------------- --- -----------                    0 BUS     1610000                    0 OPE     1320000                    0 SAL     4936000                    0 SUP     1015000                    1         8881000可以看到，为空的地方返回1，非空的地方返回0。2、使用CASE转换GROUPING()的返回值可能你会觉得前面的0和1太枯燥了，代表不了任何意义，说白了就是不够人性化，呵呵。这个时候我们可以使用CASE来转换为一些有意义的值。SQL> select  2  case grouping(division_id)  3  when 1 then 'all divisions'  4  else division_id  5  end as div,  6  sum(salary)  7  from employees2  8  group by rollup(division_id)  9  order by division_id;DIV           SUM(SALARY)------------- -----------BUS               1610000OPE               1320000SAL               4936000SUP               1015000all divisions     88810003、使用CASE和GROUPING()转换多个列的值SQL> select  2  case grouping(division_id)  3  when 1 then 'all divisions'  4  else division_id  5  end as div,  6  case grouping(job_id)  7  when 1 then 'all jobs'  8  else job_id  9  end as job, 10  sum(salary) 11  from employees2 12  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> select  2  case grouping(division_id)  3  when 1 then 'all divisions'  4  else division_id  5  end as div,  6  case grouping(job_id)  7  when 1 then 'all jobs'  8  else job_id  9  end as job, 10  sum(salary) 11  from employees2 12  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 employees2  3  group by grouping sets(division_id,job_id)  4  order by division_id,job_id;DIV JOB SUM(SALARY)--- --- -----------BUS         1610000OPE         1320000SAL         4936000SUP         1015000    ENG      245000    MGR     6246000    PRE      800000    TEC      115000    WOR     14750009 rows selected.