Oracle分析函数八——CUBE，ROLLUP

cb05098pqb

　　原创于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 studentscoreVALUES('WBQ','ENGLISH',90);
INSERT INTO studentscoreVALUES('WBQ','MATHS',95);
INSERT INTO studentscoreVALUES('WBQ','CHINESE',88);
INSERT INTO studentscoreVALUES('CZH','ENGLISH',80);
INSERT INTO studentscore VALUES('CZH','MATHS',90);
INSERT INTO studentscoreVALUES('CZH','HISTORY',92);
INSERT INTO studentscoreVALUES('CB','POLITICS',70);
INSERT INTO studentscoreVALUES('CB','HISTORY',75);
INSERT INTO studentscoreVALUES('LDH','POLITICS',80);
INSERT INTO studentscoreVALUES('LDH','CHINESE',90);
INSERT INTO studentscoreVALUES('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 ''
ENDSUMMARY,
student_name,
subjects,
sum(score)
FROM studentscore
GROUP BY CUBE(student_name,subjects)
ORDER BY 1,2;