oracle cube函数,Oracle分析函数八——CUBE，ROLLUP

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;