ORACLE ROLLUP和CUBE的使用

ORACLE ROLLUP和CUBE的使用

ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。

CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

1、向ROLLUP传递一列

SQL> select division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;

DIV SUM(SALARY)
--- -----------
BUS        1610000
OPE        1320000
SAL        4936000
SUP        1015000
              8881000

SQL>

再来看一下如果使用普通的GROUP BY，而没有ROLLUP是个什么情况

SQL> select division_id,sum(salary)
  2  from employees2
  3  group by division_id
  4  order by division_id;

DIV SUM(SALARY)
--- -----------
BUS        1610000
OPE        1320000
SAL        4936000
SUP        1015000

可以看到，缺少了最后的统计信息。

2、向ROLLUP传递多列

SQL> select division_id,job_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id,job_id)
  4  order by division_id,job_id;

DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR          530000
BUS PRE          800000
BUS WOR          280000
BUS                1610000
OPE ENG          245000
OPE MGR          805000
OPE WOR          270000
OPE                1320000
SAL MGR        4446000
SAL WOR          490000
SAL                4936000

DIV JOB SUM(SALARY)
--- --- -----------
SUP MGR          465000
SUP TEC          115000
SUP WOR          435000
SUP                1015000
                      8881000

16 rows selected.

可以看到，除了在最后有一个求和记录外，每个division_id分组也会有一个求和记录。

那么我们现在交换一下ROLLUP中数据列的顺序，看看结果怎样

SQL> select job_id,division_id,sum(salary)
  2  from employees2
  3  group by rollup(job_id,division_id)
  4  order by job_id,division_id;

JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE          245000
ENG                  245000
MGR BUS          530000
MGR OPE          805000
MGR SAL        4446000
MGR SUP          465000
MGR                6246000
PRE BUS          800000
PRE                  800000
TEC SUP          115000
TEC                  115000

JOB DIV SUM(SALARY)
--- --- -----------
WOR BUS          280000
WOR OPE          270000
WOR SAL          490000
WOR SUP          435000
WOR                1475000
                      8881000

17 rows selected.

结果类似，只不过是每一个job_id分组有一个求和记录罢了。

3、向CUBE传递一列

SQL> select division_id,sum(salary)
  2  from employees2
  3  group by cube(division_id)
  4  order by division_id;

DIV SUM(SALARY)
--- -----------
BUS        1610000
OPE        1320000
SAL        4936000
SUP        1015000
              8881000

好像和ROLLUP没什么区别哦，呵呵，继续往下看。

4、向CUBE传递多列

SQL> select job_id,division_id,sum(salary)
  2  from employees2
  3  group by cube(job_id,division_id)
  4  order by job_id,division_id;

JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE          245000
ENG                  245000
MGR BUS          530000
MGR OPE          805000
MGR SAL        4446000
MGR SUP          465000
MGR                6246000
PRE BUS          800000
PRE                  800000
TEC SUP          115000
TEC                  115000

JOB DIV SUM(SALARY)
--- --- -----------
WOR BUS          280000
WOR OPE          270000
WOR SAL          490000
WOR SUP          435000
WOR                1475000
      BUS        1610000
      OPE        1320000
      SAL        4936000
      SUP        1015000
                      8881000

21 rows selected.

可以看到工资是根据job_id和division_id求和的，CUBE在每一个job_id中都返回一条记录，表示其中的工资总数，同时在接近末尾处显示每一种division_id的工资总数，最后一条记录显示所有工资的总数。

把两列的顺序换换会怎样？呵呵，真的有兴趣那就自己动手试试吧。

转自：http://blog.csdn.net/wh62592855/article/details/4817920

====================================
以下为相关表和记录的创建脚本
====================================

CREATE TABLE divisions (
  division_id CHAR(3) CONSTRAINT divisions_pk PRIMARY KEY,
  name VARCHAR2(15) NOT NULL
);

CREATE TABLE jobs (
  job_id CHAR(3) CONSTRAINT jobs_pk PRIMARY KEY,
  name VARCHAR2(20) NOT NULL
);

CREATE TABLE employees2 (
  employee_id INTEGER CONSTRAINT employees2_pk PRIMARY KEY,
  division_id CHAR(3)
      CONSTRAINT employees2_fk_divisions
      REFERENCES divisions(division_id),
  job_id CHAR(3) REFERENCES jobs(job_id),
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  salary NUMBER(6, 0)
);

INSERT INTO divisions (
  division_id, name
) VALUES (
  'SAL', 'Sales'
);

INSERT INTO divisions (
  division_id, name
) VALUES (
  'OPE', 'Operations'
);

INSERT INTO divisions (
  division_id, name
) VALUES (
  'SUP', 'Support'
);

INSERT INTO divisions (
  division_id, name
) VALUES (
  'BUS', 'Business'
);
INSERT INTO jobs (
  job_id, name
) VALUES (
  'WOR', 'Worker'
);

INSERT INTO jobs (
  job_id, name
) VALUES (
  'MGR', 'Manager'
);

INSERT INTO jobs (
  job_id, name
) VALUES (
  'ENG', 'Engineer'
);

INSERT INTO jobs (
  job_id, name
) VALUES (
  'TEC', 'Technologist'
);

INSERT INTO jobs (
  job_id, name
) VALUES (
  'PRE', 'President'
);
INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  1, 'BUS', 'PRE', 'James', 'Smith', 800000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  2, 'SAL', 'MGR', 'Ron', 'Johnson', 350000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  3, 'SAL', 'WOR', 'Fred', 'Hobbs', 140000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  4, 'SUP', 'MGR', 'Susan', 'Jones', 200000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  5, 'SAL', 'WOR', 'Rob', 'Green', 350000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  6, 'SUP', 'WOR', 'Jane', 'Brown', 200000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  7, 'SUP', 'MGR', 'John', 'Grey', 265000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  8, 'SUP', 'WOR', 'Jean', 'Blue', 110000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  9, 'SUP', 'WOR', 'Henry', 'Heyson', 125000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  10, 'OPE', 'MGR', 'Kevin', 'Black', 225000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  11, 'OPE', 'MGR', 'Keith', 'Long', 165000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  12, 'OPE', 'WOR', 'Frank', 'Howard', 125000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  13, 'OPE', 'WOR', 'Doreen', 'Penn', 145000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  14, 'BUS', 'MGR', 'Mark', 'Smith', 155000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  15, 'BUS', 'MGR', 'Jill', 'Jones', 175000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  16, 'OPE', 'ENG', 'Megan', 'Craig', 245000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  17, 'SUP', 'TEC', 'Matthew', 'Brant', 115000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  18, 'OPE', 'MGR', 'Tony', 'Clerke', 200000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  19, 'BUS', 'MGR', 'Tanya', 'Conway', 200000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  20, 'OPE', 'MGR', 'Terry', 'Cliff', 215000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  21, 'SAL', 'MGR', 'Steve', 'Green', 275000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  22, 'SAL', 'MGR', 'Roy', 'Red', 375000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  23, 'SAL', 'MGR', 'Sandra', 'Smith', 335000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  24, 'SAL', 'MGR', 'Gail', 'Silver', 225000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  25, 'SAL', 'MGR', 'Gerald', 'Gold', 245000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  26, 'SAL', 'MGR', 'Eileen', 'Lane', 235000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  27, 'SAL', 'MGR', 'Doreen', 'Upton', 235000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  28, 'SAL', 'MGR', 'Jack', 'Ewing', 235000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  29, 'SAL', 'MGR', 'Paul', 'Owens', 245000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  30, 'SAL', 'MGR', 'Melanie', 'York', 255000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  31, 'SAL', 'MGR', 'Tracy', 'Yellow', 225000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  32, 'SAL', 'MGR', 'Sarah', 'White', 235000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  33, 'SAL', 'MGR', 'Terry', 'Iron', 225000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  34, 'SAL', 'MGR', 'Christine', 'Brown', 247000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  35, 'SAL', 'MGR', 'John', 'Brown', 249000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  36, 'SAL', 'MGR', 'Kelvin', 'Trenton', 255000
);

INSERT INTO employees2 (
  employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
  37, 'BUS', 'WOR', 'Damon', 'Jones', 280000
);