Oracle分析函数八——CUBE，ROLLUP

最新推荐文章于 2022-01-20 15:10:58 发布

最新推荐文章于 2022-01-20 15:10:58 发布 · 83 阅读

文章标签：

#数据库

本文深入解析了Oracle数据库中的两个分析函数CUBE和ROLLUP的功能及使用方法，通过具体实例展示了如何利用这两个函数进行多维度的数据汇总，同时对比了CUBE与ROLLUP在GROUP BY操作上的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;