Oracle分析函数八——CUBE，ROLLUP

原创于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;