Group分组及其扩展总结（二）

--1.Grouping 函数可以接受一列,返回0或者1 如果列值为空那么返回1 否则返回0
select grouping(a.division_id),a.division_id, sum(a.salary)
  from employees2 a
 group by rollup(a.division_id)
 order by a.division_id
 
 运行结果为：
  GROUPING(A.DIVISION_ID)    DIVISION_ID    SUM(A.SALARY)
1    0    BUS    1610000
2    0    OPE    1320000
3    0    SAL    4936000
4    0    SUP    1015000
5    1          8881000

--加入grouping后的效果
select case grouping(a.division_id)
         when 0 then a.division_id
         when 1 then '总计'
       end 部门,
       sum(a.salary)
  from employees2 a
 group by rollup(a.division_id)
 order by a.division_id
运行结果为：
  部门    SUM(A.SALARY)
1    BUS    1610000
2    OPE    1320000
3    SAL    4936000
4    SUP    1015000
5    总计    8881000

注意事项：grouping仅在rollup和cube子句中使用得到，用于将空值转换为一个有意义的值

--2.使用case和grouping转换多个列的值
select case grouping(b.division_id)
         when 1 then 'all division'
         else b.division_id
         end as div_id,
       case grouping(b.job_id)
         when 1 then 'all jobs'
         else b.job_id
         end as job, 
       sum(b.salary)
  from employees2 b
  group by rollup(b.division_id,b.job_id)
  order by b.division_id, b.job_id

运行结果：
DIV_ID    JOB    SUM(B.SALARY)
1    BUS    MGR    530000
2    BUS    PRE    800000
3    BUS    WOR    280000
4    BUS    all jobs    1610000
5    OPE    ENG    245000
6    OPE    MGR    805000
7    OPE    WOR    270000
8    OPE    all jobs    1320000
9    SAL    MGR    4446000
10    SAL   WOR    490000
11    SAL   all jobs    4936000
12    SUP   MGR    465000
13    SUP   TEC    115000
14    SUP   WOR    435000
15    SUP   all jobs    1015000
16    all   divisionall jobs    8881000

--3.grouping和cube联合使用
select case grouping(b.division_id)
         when 1 then 'all division'
         else b.division_id
         end as div_id,
       case grouping(b.job_id)
         when 1 then 'all jobs'
         else b.job_id
         end as job, 
       sum(b.salary)
  from employees2 b
  group by cube(b.division_id,b.job_id)
  order by b.division_id, b.job_id

运行结果

  DIV_ID    JOB    SUM(B.SALARY)
1    BUS    MGR    530000
2    BUS    PRE    800000
3    BUS    WOR    280000
4    BUS    all jobs    1610000
5    OPE    ENG    245000
6    OPE    MGR    805000
7    OPE    WOR    270000
8    OPE    all jobs    1320000
9    SAL    MGR    4446000
10    SAL    WOR    490000
11    SAL    all jobs    4936000
12    SUP    MGR    465000
13    SUP    TEC    115000
14    SUP    WOR    435000
15    SUP    all jobs    1015000
16    all division    ENG    245000
17    all division    MGR    6246000
18    all division    PRE    800000
19    all division    TEC    115000
20    all division    WOR    1475000
21    all division    all jobs    8881000

 

--4.Grouping sets子句只返回小计信息
select case grouping(b.division_id)
         when 1 then 'all division'
         else b.division_id
         end as div_id,
       case grouping(b.job_id)
         when 1 then 'all jobs'
         else b.job_id
         end as job, 
       sum(b.salary)
  from employees2 b
  group by grouping sets(b.division_id,b.job_id)
  order by b.division_id, b.job_id
运行结果
       DIV_ID    JOB    SUM(B.SALARY)
1    BUS    all jobs    1610000
2    OPE    all jobs    1320000
3    SAL    all jobs    4936000
4    SUP    all jobs    1015000
5    all division    ENG    245000
6    all division    MGR    6246000
7    all division    PRE    800000
8    all division    TEC    115000
9    all division    WOR    1475000