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的工資總數，最後一條記錄顯示所有工資的總數。

把兩列的順序換換會怎樣？呵呵，真的有興趣那就自己動手試試吧。

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

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