函数---GROUPING
Java代码
GROUPING
GROUPING函数可以接受一列，返回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 1610000
OPE 1320000
SAL 4936000
SUP 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 1610000
OPE 1320000
SAL 4936000
SUP 1015000
all divisions 8881000
3、使用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 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
DIV JOB SUM(SALARY)
------------- -------- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions all jobs 8881000
16 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 530000
BUS PRE 800000
BUS WOR
补充：软件开发 , Java ,
