Oracle分组函数之高效的ROLLUP
㈠ 初始化实验坏境
www.2cto.com
[sql]
hr@ORCL> create table rollup_test as
2 select e.department_id,j.job_title,e.first_name,e.salary
3 from employees e,jobs j
4 where e.job_id=j.job_id;
Table created.
hr@ORCL> select * from rollup_test;
DEPARTMENT_ID JOB_TITLE FIRST_NAME SALARY
------------- ----------------------------------- -------------------- ----------
50 Shipping Clerk Donald 2600
50 Shipping Clerk Douglas 2600
10 Administration Assistant Jennifer 4400
20 Marketing Manager Michael 13000
20 Marketing Representative Pat 6000
40 Human Resources Representative Susan 6500
70 Public Relations Representative Hermann 10000
110 Accounting Manager Shelley 12000
110 Public Accountant William 8300
90 President Steven 24000
90 Administration Vice President Neena 17000
90 Administration Vice President Lex 17000
60 Programmer Alexander 9000
60 Programmer Bruce 6000
60 Programmer David 4800
60 Programmer Valli 4800
60 Programmer Diana 4200
100 Finance Manager Nancy 12000
100 Accountant Daniel 9000
100 Accountant John 8200
100 Accountant Ismael 7700
100 Accountant Jose Manuel 7800
100 Accountant Luis 6900
30 Purchasing Manager Den 11000
30 Purchasing Clerk Alexander 3100
30 Purchasing Clerk Shelli 2900
30 Purchasing Clerk Sigal 2800
30 Purchasing Clerk Guy 2600
30 Purchasing Clerk Karen 2500
50 Stock Manager Matthew 8000
...............................................
...............................................
...............................................
www.2cto.com
㈡ 先看一下普通分组的效果：对DEPARTMENT_ID进行普通的GROUP BY操作---按照小组进行分组
[sql]
hr@ORCL> select department_id,sum(salary) from rollup_test group by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51600
30 24900
7000
20 19000
70 10000
90 58000
110 20300
50 156400
40 6500
80 304500
10 4400
60 28800
12 rows selected.
㈢ 对DEPARTMENT_ID进行普通的ROLLUP操作---按照小组进行分组，同时求总计
[sql]
hr@ORCL> select department_id,sum(salary) from rollup_test group by rollup(department_id);
DEPARTMENT_ID SUM