ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分析函数八——CUBE,ROLLUP

Oracle分析函数八——CUBE,ROLLUP

Linux操作系统 作者:蓝色妖姬09 时间:2013-11-18 14:08:59 0 删除 编辑
Oracle分析函数——CUBEROLLUP

CUBE

功能描述:

注意:

 

ROLLUP

功能描述:

注意:

 

如果是ROLLUP(A, B, C)的话,GROUP BY顺序

(ABC)

(AB)

(A)

最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)GROUP BY顺序

(ABC)

(AB)

(AC)

(A)

(BC)

(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;

 

image058.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29310145/viewspace-776845/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-11-18

  • 博文量
    8
  • 访问量
    7761