ORACLE ROLLUP和CUBE的使用

忘是亡心i 2021-06-24 16:10 405阅读 0赞

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)  
\--- --- -----------  
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的工资总数，最后一条记录显示所有工资的总数。

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

==================================================================================

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