Oracle分析函数八——CUBE,ROLLUP

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/baoqiangwang/article/details/4712533

原创于2009年08月02日,2009年10月22日迁移至此。


Oracle 分析函数——CUBE ROLLUP

CUBE

功能描述:

注意:

 

ROLLUP

功能描述:

注意:

 

如果是 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 操作。

 

CREATE TABLE studentscore

(

 student_name varchar2(20),

 subjects varchar2(20),

 score number

)

INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);

INSERT INTO studentscore VALUES('WBQ','MATHS',95);

INSERT INTO studentscore VALUES('WBQ','CHINESE',88);

INSERT INTO studentscore VALUES('CZH','ENGLISH',80);

INSERT INTO studentscore VALUES('CZH','MATHS',90);

INSERT INTO studentscore VALUES('CZH','HISTORY',92);

INSERT INTO studentscore VALUES('CB','POLITICS',70);

INSERT INTO studentscore VALUES('CB','HISTORY',75);

INSERT INTO studentscore VALUES('LDH','POLITICS',80);

INSERT INTO studentscore VALUES('LDH','CHINESE',90);

INSERT INTO studentscore VALUES('LDH','HISTORY',95);

 

SELECT

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY CUBE(student_name,subjects);

等同于以下标准 SQL

SELECT NULL,subjects,SUM(score)

 FROM studentscore

 GROUP BY subjects

 UNION

SELECT student_name,NULL,SUM(score)

 FROM studentscore

 GROUP BY student_name

 UNION

SELECT NULL,NULL,SUM(score)

 FROM studentscore

 UNION

SELECT student_name,subjects,SUM(score)

 FROM studentscore

 GROUP BY student_name,subjects

 

SELECT

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY ROLLUP(student_name,subjects);

 

SELECT student_name,NULL,SUM(score)

 FROM studentscore

 GROUP BY student_name

 UNION

SELECT NULL,NULL,SUM(score)

 FROM studentscore

 UNION

SELECT student_name,subjects,SUM(score)

 FROM studentscore

 GROUP BY student_name,subjects

 

 

SELECT

 grouping(student_name),

 grouping(subjects), 

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY CUBE(student_name,subjects)

 ORDER BY 1,2;

 

SELECT

 grouping(student_name),

 grouping(subjects), 

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY ROLLUP(student_name,subjects)

 ORDER BY 1,2;

 

SELECT

 grouping_id(student_name,subjects),  

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY CUBE(student_name,subjects)

 ORDER BY 1;

 

SELECT

 grouping_id(student_name,subjects),  

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY ROLLUP(student_name,subjects)

 ORDER BY 1;

 

SELECT

 grouping(student_name),

 grouping(subjects),

 CASE WHEN grouping(student_name)=0 AND grouping(subjects)=1 THEN ' 学生成绩合计 '

      WHEN grouping(student_name)=1 AND grouping(subjects)=0 THEN ' 课目成绩合计 '

      WHEN grouping(student_name)=1 AND grouping(subjects)=1 THEN '                 '

      ELSE ''

 END SUMMARY,

 student_name,

 subjects,

 sum(score)

 FROM studentscore

 GROUP BY CUBE(student_name,subjects)

 ORDER BY 1,2;

展开阅读全文

没有更多推荐了,返回首页