本文通过例子展示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;

CLASSCOURSESTU_NOSTU_NAMESCORE
Class_AMath10001Tough195
Class_AMath10002Tough293
Class_BMath10003Tough394
Class_BMath10004Tough488
Class_AComputer10001Tough189
Class_AComputer10002Tough298
Class_BComputer10003Tough389
Class_BComputer10004Tough487
  • sum函数

按class,course分组,然后对score统计总合

CLASSCOURSEAVG_SCORE
Class_BMath91
Class_AComputer93.5
Class_AMath94
Class_BComputer88
  • rollup函数

先按class,course分组汇总;再按class分组汇总;最后全表分组汇总

CLASSCOURSEAVG_SCORE
Class_AMath94
Class_AComputer93.5
Class_A 93.75
Class_BMath91
Class_BComputer88
Class_B 89.5
  91.625
  • cube函数

先按class,course分组汇总;再按class分组汇总;再按course分组汇总;最后全表分组汇总

CLASSCOURSEAVG_SCORE
Class_AComputer93.5
Class_AMath94
Class_A 93.75
Class_BComputer88
Class_BMath91
Class_B 89.5
 Computer90.75
 Math92.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 ClassAll Course91.625
All ClassComputer90.75
All ClassMath92.5
Class_AAll Course93.75
Class_AComputer93.5
Class_AMath94
Class_BAll Course89.5
Class_BComputer88
Class_BMath91

05-02 22:47