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

 

--1.单独使用Group By子句：每个分组返回一条记录
select a.division_id, sum(a.salary)
  from employees2 a
 group by a.division_id
 order by a.division_id

运行结果:

   DIVISION_ID    SUM(A.SALARY)
1    BUS    1610000
2    OPE    1320000
3    SAL    4936000
4    SUP    1015000

--2.扩展子句Rollup: 每个分组返回一条小计,最后返回一条总计
select a.division_id, sum(a.salary)
  from employees2 a
 group by rollup(a.division_id)
 order by a.division_id

运行结果：最后多了一行总计

   DIVISION_ID    SUM(A.SALARY)
1    BUS    1610000
2    OPE    1320000
3    SAL    4936000
4    SUP    1015000
5        8881000

 

--2.1向Rollup传递多列:按照第一列给出小计,最后有所有分组的小计
select b.division_id,b.job_id, sum(b.salary)
  from employees2 b
 group by rollup(b.division_id, b.job_id)
 order by b.division_id, b.job_id

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

注意：其他聚合函数也可以和rollup一起使用,rollup使用要注意的就是列的顺序比较重要。

--3:扩展子句2：Cube子句:返回所有列组合的小计并在最后给出总计
select b.division_id, b.job_id, sum(b.salary)
  from employees2 b
 group by cube(b.division_id,b.job_id)
 order by b.division_id, b.job_id

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

调换列的顺序后仅是显示的顺序不同，不影响查询的结果。