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

 1 --1.Grouping 函数可以接受一列,返回0或者1 如果列值为空那么返回1 否则返回0
 2 select grouping(a.division_id),a.division_id, sum(a.salary)
 3   from employees2 a
 4  group by rollup(a.division_id)
 5  order by a.division_id
 6  
 7  运行结果为：
 8   GROUPING(A.DIVISION_ID)    DIVISION_ID    SUM(A.SALARY)
 9 1    0    BUS    1610000
10 2    0    OPE    1320000
11 3    0    SAL    4936000
12 4    0    SUP    1015000
13 5    1          8881000
14 
15 --加入grouping后的效果
16 select case grouping(a.division_id)
17          when 0 then a.division_id
18          when 1 then '总计'
19        end 部门,
20        sum(a.salary)
21   from employees2 a
22  group by rollup(a.division_id)
23  order by a.division_id
24 运行结果为：
25   部门    SUM(A.SALARY)
26 1    BUS    1610000
27 2    OPE    1320000
28 3    SAL    4936000
29 4    SUP    1015000
30 5    总计    8881000
31 
32 注意事项：grouping仅在rollup和cube子句中使用得到，用于将空值转换为一个有意义的值

 1 --2.使用case和grouping转换多个列的值
 2 select case grouping(b.division_id)
 3          when 1 then 'all division'
 4          else b.division_id
 5          end as div_id,
 6        case grouping(b.job_id)
 7          when 1 then 'all jobs'
 8          else b.job_id
 9          end as job, 
10        sum(b.salary)
11   from employees2 b
12   group by rollup(b.division_id,b.job_id)
13   order by b.division_id, b.job_id
14 
15 运行结果：
16 DIV_ID    JOB    SUM(B.SALARY)
17 1    BUS    MGR    530000
18 2    BUS    PRE    800000
19 3    BUS    WOR    280000
20 4    BUS    all jobs    1610000
21 5    OPE    ENG    245000
22 6    OPE    MGR    805000
23 7    OPE    WOR    270000
24 8    OPE    all jobs    1320000
25 9    SAL    MGR    4446000
26 10    SAL   WOR    490000
27 11    SAL   all jobs    4936000
28 12    SUP   MGR    465000
29 13    SUP   TEC    115000
30 14    SUP   WOR    435000
31 15    SUP   all jobs    1015000
32 16    all   divisionall jobs    8881000

 1 --3.grouping和cube联合使用
 2 select case grouping(b.division_id)
 3          when 1 then 'all division'
 4          else b.division_id
 5          end as div_id,
 6        case grouping(b.job_id)
 7          when 1 then 'all jobs'
 8          else b.job_id
 9          end as job, 
10        sum(b.salary)
11   from employees2 b
12   group by cube(b.division_id,b.job_id)
13   order by b.division_id, b.job_id
14 
15 运行结果
16 
17   DIV_ID    JOB    SUM(B.SALARY)
18 1    BUS    MGR    530000
19 2    BUS    PRE    800000
20 3    BUS    WOR    280000
21 4    BUS    all jobs    1610000
22 5    OPE    ENG    245000
23 6    OPE    MGR    805000
24 7    OPE    WOR    270000
25 8    OPE    all jobs    1320000
26 9    SAL    MGR    4446000
27 10    SAL    WOR    490000
28 11    SAL    all jobs    4936000
29 12    SUP    MGR    465000
30 13    SUP    TEC    115000
31 14    SUP    WOR    435000
32 15    SUP    all jobs    1015000
33 16    all division    ENG    245000
34 17    all division    MGR    6246000
35 18    all division    PRE    800000
36 19    all division    TEC    115000
37 20    all division    WOR    1475000
38 21    all division    all jobs    8881000

 

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

 

转载于:https://www.cnblogs.com/zhanglt/archive/2012/11/04/2753392.html