Oracle分组函数之高效的ROLLUP(五)
7 | 1391 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1513 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
33 rows processed
㈦ 补充一步，体验一下GROUPING函数的效果
[sql]
SQL> select department_id,job_title,grouping(department_id),grouping(job_title),sum(salary)
2 from rollup_test group by rollup(department_id,job_title);
DEPARTMENT_ID JOB_TITLE GROUPING(DEPARTMENT_ID) GROUPING(JOB_TITLE) SUM(SALARY)
------------- ----------------------------------- ----------------------- ------------------- -----------
Sales Representative 0 0 7000
0 1 7000
10 Administration Assistant 0 0 4400
10 0 1 4400
20 Marketing Manager 0 0 13000
20 Marketing Representative 0 0 6000
20 0 1 19000
30 Purchasing Clerk 0 0 13900
30 Purchasing Manager 0 0 11000
30 0 1 24900
40 Human Resources Representative 0 0 6500
40 0 1 6500
50 Stock Clerk 0 0 55700
50 Stock Manager 0 0 36400
50 Shipping Clerk 0 0 64300
50 0 1 156400
60 Programmer 0 0 28800
60 0 1 28800
70 Public Relations Representative 0 0 10000
70 0 1 10000
80 Sales Manager 0 0 61000
80 Sales Representative 0 0 243500
80 0 1 304500
90 President 0 0 24000
90 Administration Vice President 0 0 34000
90 0 1 58000
100 Accountant 0 0 39600
100 Finance Manager 0 0 12000
100 0 1 51600
110 Public Accountant 0 0 8300
110 Accounting Manager 0 0 12000