分组函数的扩展使用
1.ROLLUP子句
ROLLUP是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明：
EG：
select * from employees2;
EMPLOYEE_ID DIVISI JOB_ID FIRST_NAME LAST_NAME SALARY
----------- ------ ------ -------------------- -------------------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred
Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
6 SUP WOR Jane Brown 200000
7 SUP MGR John Grey 265000
8 SUP WOR Jean Blue 110000
9 SUP WOR Henry Heyson 125000
10 OPE MGR Kevin Black 225000
11 OPE MGR Keith Long 165000
12 OPE WOR Frank Howard 125000
13 OPE WOR Doreen Penn 145000
14 BUS MGR Mark Smith 155000
15 BUS MGR Jill Jones 175000
16 OPE ENG Megan Craig 245000
17 SUP TEC Matthew Brant 115000
18 OPE MGR Tony Clerke 200000
19 BUS MGR Tanya
Conway 200000
20 OPE MGR Terry Cliff 215000
21 SAL MGR Steve Green 275000
22 SAL MGR
Roy Red 375000
23 SAL MGR Sandra Smith 335000
24 SAL MGR Gail Silver 225000
25 SAL MGR Gerald Gold 245000
26 SAL MGR Eileen Lane
235000
27 SAL MGR Doreen
Upton 235000
28 SAL MGR Jack
Ewing 235000
29 SAL MGR Paul Owens 245000
30 SAL MGR Melanie
York 255000
31 SAL MGR
Tracy Yellow 225000
32 SAL MGR Sarah White 235000
33 SAL MGR Terry Iron 225000
34 SAL MGR Christine Brown 247000
35 SAL MGR John Brown 249000
36 SAL MGR Kelvin
Trenton 255000
37 BUS WOR Damon Jones 280000
已选择37行。
首先我们来看单独的GROUP BY语句：
select division_id,SUM(salary) from employees2
group by division_id;
DIVISI SUM(SALARY)
------ -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
接下来是扩展了的ROLLUP的使用：
select division_id,SUM(salary) from employees2
group by ROLLUP(division_id);
DIVISI SUM(SALARY)
------ -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
再来看个例子：
select division_id,job_id,SUM(salary)
from employees2
group by ROLLUP(division_id,job_id);
DIVISI JOB_ID SUM(SALARY)
------ ------ -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
8881000
小结：扩展了一个rollup子句带来了分组的一个合计。
2.CUBE子句
CUBE子句也是对GROUP BY子句进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。
EG：
select division_id,job_id,SUM(salary)
from employees2
group by CUBE(division_id,job_id)
order by division_id;
DIVISI JOB_ID SUM(SALARY)
------ ------ -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
8881000
结：CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息（job_id）
3.GROUPING()函数与ROLLUP、CUBE的结合使用
GROUPING（）可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。（注意：GROUPING只能在ROLLUP、CUBE查询中使用。还可以结合DECODE（）函数来注释使用更好）
现在我们把上面的一个例子改一下;
select GROUPING(division_id), division_id,SUM(salary)
from employees2
group by ROLLUP(division_id);
GROUPING(DIVISION_ID) DIVISI SUM(SALARY)
--------------------- ------ -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
下面再用DECODE（）函数来转换下更好：
select DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,
division_id,SUM(salary)
from employees2
group by ROLLUP(division_id);
DIV DIVISI SUM(SALARY)
-------------------------- ------ -----------
BUS BUS 1610000
OPE OPE 1320000
SAL SAL 4936000
SUP SUP 1015000
All divisions 8881000
哈哈，这样看下更清晰明了
再举例CUBE与GROUPING（）结合的使用
select
DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,
DECODE(GROUPING(job_id),1,'All jobs',job_id) AS job,
SUM(salary)
from employees2
group by CUBE(division_id,job_id)
order by division_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
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP All jobs 1015000
All divisions ENG 245000
All divisions