本文通过例子展示sum, rollup, cube, grouping的用法。
//首先建score表
create table score(
class nvarchar2(20),
course nvarchar2(20),
stu_no number(5),
stu_name nvarchar2(20),
score number(2));
//插入数据
insert into score values ('Class_A','Math',10001,'Tough1',95);
insert into score values ('Class_A','Math',10002,'Tough2',93);
insert into score values ('Class_B','Math',10003,'Tough3',94);
insert into score values ('Class_B','Math',10004,'Tough4',88);
insert into score values ('Class_A','Computer',10001,'Tough1',89);
insert into score values ('Class_A','Computer',10002,'Tough2',98);
insert into score values ('Class_B','Computer',10003,'Tough3',89);
insert into score values ('Class_B','Computer',10004,'Tough4',87);
SELECT * FROM score;
CLASS | COURSE | STU_NO | STU_NAME | SCORE |
Class_A | Math | 10001 | Tough1 | 95 |
Class_A | Math | 10002 | Tough2 | 93 |
Class_B | Math | 10003 | Tough3 | 94 |
Class_B | Math | 10004 | Tough4 | 88 |
Class_A | Computer | 10001 | Tough1 | 89 |
Class_A | Computer | 10002 | Tough2 | 98 |
Class_B | Computer | 10003 | Tough3 | 89 |
Class_B | Computer | 10004 | Tough4 | 87 |
- sum函数
按class,course分组,然后对score统计总合
CLASS | COURSE | AVG_SCORE |
Class_B | Math | 91 |
Class_A | Computer | 93.5 |
Class_A | Math | 94 |
Class_B | Computer | 88 |
- rollup函数
先按class,course分组汇总;再按class分组汇总;最后全表分组汇总
CLASS | COURSE | AVG_SCORE |
Class_A | Math | 94 |
Class_A | Computer | 93.5 |
Class_A | 93.75 | |
Class_B | Math | 91 |
Class_B | Computer | 88 |
Class_B | 89.5 | |
91.625 |
- cube函数
先按class,course分组汇总;再按class分组汇总;再按course分组汇总;最后全表分组汇总
CLASS | COURSE | AVG_SCORE |
Class_A | Computer | 93.5 |
Class_A | Math | 94 |
Class_A | 93.75 | |
Class_B | Computer | 88 |
Class_B | Math | 91 |
Class_B | 89.5 | |
Computer | 90.75 | |
Math | 92.5 | |
91.625 |
rollup和cube区别:
ROLLUP(A,B,C)的话,GROUP BY顺序
(A,B,C)
(A,B)
(A)
最后对全表进行GROUP BY操作。
GROUP BY CUBE(A, B, C),GROUP BY顺序
(A,B,C)
(A,B)
(A,C)
(A),
(B,C)
(B)
(C),
最后对全表进行GROUP BY操作。
- grouping函数
rollup和cube函数都会对结果集产生null,这时候可用grouping函数来确认该记录是由哪个字段得出来的。grouping函数用法,带一个参数,参数为字段名,结果是根据该字段得出来的就返回1,反之返回0
All Class | All Course | 91.625 |
All Class | Computer | 90.75 |
All Class | Math | 92.5 |
Class_A | All Course | 93.75 |
Class_A | Computer | 93.5 |
Class_A | Math | 94 |
Class_B | All Course | 89.5 |
Class_B | Computer | 88 |
Class_B | Math | 91 |