1.初始化实验坏境
1)创建测试表group_test
SECOOLER@ora11g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
Table created.
2)初始化数据
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director', 'Hill',1000);
insert into group_test values (20,'Coding', 'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director', 'Michael',2000);
insert into group_test values (30,'Coding', 'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director', 'Sabrina',3000);
insert into group_test values (40,'Coding', 'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director', 'Wendy',4000);
commit;
3)初始化之后的数据情况如下:
SECOOLER@ora11g> set pages 100
SECOOLER@ora11g> select * from group_test;
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000
16 rows selected.
2.先看一下普通分组的效果:对group_id进行普通的group by操作---按照小组进行分组
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id;
GROUP_ID SUM(SALARY)
---------- -----------
30 12000
20 8000
40 16000
10 4000
3.对group_id进行普通的roolup操作---按照小组进行分组,同时求总计
rollup和group by的区别是:rollup最后会求总计
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
使用Group By语句翻译一下上面的SQL语句如下(union all一个统计所有数据的行):
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id
2 union all
3 select null, sum(salary) from group_test
#总计那一行,第一列group_id是空的,故select null,sum...
4 order by 1; #按第1列排序,即按group_id排序GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
4.再看一个rollup两列的情况
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);
#rollup(group_id,job)按group_id,job分组且最后求总计(按group_id,job后每个组的总计)
GROUP_ID JOB SUM(SALARY)---------- ---------- -----------
10 Coding 1000
10 Director 1000
10 Architect 1000
10 Programmer 1000
10 4000
20 Coding 2000
20 Director 2000
20 Architect 2000
20 Programmer 2000
20 8000
30 Coding 3000
30 Director 3000
30 Architect 3000
30 Programmer 3000
30 12000
40 Coding 4000
40 Director 4000
40 Architect 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
上面的SQL语句该如何使用Group By进行翻译呢?
答案如下:
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by group_id, job
2 union all
3 select group_id,null,sum(salary) from group_test group by group_id
4 union all
5 select null,null,sum(salary) from group_test
6 order by 1,2; #按第一列和第2列排序即(group_id,job)
#第一个union all:select group_id,null,sum(salary) from group_test group by group_id
它是相当于group_id分组后每一个小项汇总,它的group_id是有值(下面的10),job没值,故select语句中select
group_id,null,
#第二个union all:是最后一行汇总,它的group_id,job全是空的,故select null,null,sum...
GROUP_ID JOB SUM(SALARY)---------- ---------- -----------
10 Architect 1000
10 Coding 1000
10 Director 1000
10 Programmer 1000
10 4000
20 Architect 2000
20 Coding 2000
20 Director 2000
20 Programmer 2000
20 8000
30 Architect 3000
30 Coding 3000
30 Director 3000
30 Programmer 3000
30 12000
40 Architect 4000
40 Coding 4000
40 Director 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
5.补充一步,体验一下GROUPING函数的效果
grouping只是这个值在这一行中是原来的值,还是汇总的值,若是原来的值,则为0,若是汇总后的值,则为1.
下面的红色字体:10 0 1 4000为例,此条数据,group(group_ID)还是原来10,故为0;grouping(JOB)这行的job
是空值,不是前面的Coding之类的,故为1,因为它是汇总小计列。最后一行总计也是类似。
直接看效果就OK啦:SECOOLER@ora11g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Coding 0 0 1000
10 Director 0 0 1000
10 Architect 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Architect 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Architect 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Architect 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
1 1 40000
21 rows selected.
看出来什么效果了么?
有的同学还是没有看出来,小小的解释一下:
如果显示“1”表示GROUPING函数对应的列(例如JOB字段)是由于ROLLUP函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。
如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。
补充例题:自己解释如下
select sum(salary),department,bonus,group_id() from employees
where bonus > 100
group by department,
rollup(department,bouns);
应该是这样的:group_id()去重,rollup(department,bonus)是按department,bonus分组后汇总(24000)
sum(salary) department bonus
4000 10 200
8000 20 400
12000 30 700
24000
SELECT SUM(salary), department, bonus, GROUP_ID()
FROM employees
WHERE bonus > 100
GROUP BY department,
ROLLUP (department, bonus)
HAVING GROUP_ID() < 1;
注意:HAVING GROUP_ID() < 1 #是指所有值都是非空值,若是空值是为0,非空值为1,group_id是按二进制来计算的
例如:GROUP_ID(department,bonus),deparment的值为非空,是1,非空为0。 假如deparment 为非空(1),bonus为空(0)
则GROUP_ID(department,bonus)为2(10转二进制为2).