Oracle分组函数之高效的ROLLUP(四)
redo size
1511 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
㈥ 上面的SQL语句该如何使用Group By进行翻译呢？
[sql]
hr@ORCL> select department_id,job_title,sum(salary) from rollup_test group by department_id,job_title
2 union all
3 select department_id,null,sum(salary) from rollup_test group by department_id
4 union all
5 select null,null,sum(salary) from rollup_test
6 order by 1,2;
DEPARTMENT_ID JOB_TITLE SUM(SALARY)
------------- ----------------------------------- -----------
10 Administration Assistant 4400
10 4400
20 Marketing Manager 13000
20 Marketing Representative 6000
20 19000
30 Purchasing Clerk 13900
30 Purchasing Manager 11000
30 24900
40 Human Resources Representative 6500
40 6500
50 Shipping Clerk 64300
50 Stock Clerk 55700
50 Stock Manager 36400
50 156400
60 Programmer 28800
60 28800
70 Public Relations Representative 10000
70 10000
80 Sales Manager 61000
80 Sales Representative 243500
80 304500
90 Administration Vice President 34000
90 President 24000
90 58000
100 Accountant 39600
100 Finance Manager 12000
100 51600
110 Accounting Manager 12000
110 Public Accountant 8300
110 20300
Sales Representative 7000
691400
7000
33 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2979879831
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 215 | 7610 | 12 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 215 | 7610 | 11 (73)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH GROUP BY | | 107 | 4815 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| ROLLUP_TEST | 107 | 4815 | 3 (0)| 00:00:01 |
| 5 | HASH GROUP BY | | 107 | 2782 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| ROLLUP_TEST | 107 | 2782 | 3 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| ROLLUP_TEST | 10