Oracle PL/SQL之GROUP BY GROUPING SETS

Oracle PL/SQL之GROUP BY GROUPING SETS
[转自] http://blog.csdn.net/t0nsha/article/details/6538838

使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来。

当GROUPING SETS里面的分组元素越多时，使用GROUPING SETS比使用UNION ALL性能更好，这可能和使用GROUPING SETS只需要访问一次表有关。

如下两段查询的结果是相等的：

q1（GROUPING SETS）：
[c-sharp] view plain copy print ?
1. SELECT department_id, job_id, manager_id, AVG (salary)
2. FROM employees
3. GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));
6. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
7. ------------- ---------- ---------- -----------
8. SH_CLERK 122 3200
9. AC_MGR 101 12000
10. ST_MAN 100 7280
11. ST_CLERK 121 2675
12. SA_REP 148 8650
13. SH_CLERK 120 2900
14. SH_CLERK 124 2825
15. MK_MAN 100 13000
16. AD_PRES 24000
17. FI_MGR 101 12000
18. SA_REP 146 8500
19. SH_CLERK 123 3475
20. AD_ASST 101 4400
21. IT_PROG 102 9000
22. IT_PROG 103 4950
23. FI_ACCOUNT 108 7920
24. PU_MAN 100 11000
25. ST_CLERK 122 2700
26. SA_REP 145 8500
27. AC_ACCOUNT 205 8300
28. AD_VP 100 17000
30. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
31. ------------- ---------- ---------- -----------
32. ST_CLERK 120 2625
33. ST_CLERK 124 2925
34. SA_REP 147 7766.66667
35. SA_REP 149 8333.33333
36. HR_REP 101 6500
37. PR_REP 101 10000
38. ST_CLERK 123 3000
39. SH_CLERK 121 3675
40. PU_CLERK 114 2780
41. SA_MAN 100 12200
42. MK_REP 201 6000
43. 110 AC_ACCOUNT 8300
44. 90 AD_VP 17000
45. 50 ST_CLERK 2785
46. 80 SA_REP 8396.55172
47. 50 ST_MAN 7280
48. 80 SA_MAN 12200
49. 110 AC_MGR 12000
50. 90 AD_PRES 24000
51. 60 IT_PROG 5760
52. 100 FI_MGR 12000
54. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
55. ------------- ---------- ---------- -----------
56. 30 PU_CLERK 2780
57. 50 SH_CLERK 3215
58. 20 MK_MAN 13000
59. 100 FI_ACCOUNT 7920
60. SA_REP 7000
61. 70 PR_REP 10000
62. 30 PU_MAN 11000
63. 10 AD_ASST 4400
64. 20 MK_REP 6000
65. 40 HR_REP 6500
67. 52 rows selected.
SELECT department_id, job_id, manager_id, AVG (salary) FROM employees GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id)); DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- SH_CLERK 122 3200 AC_MGR 101 12000 ST_MAN 100 7280 ST_CLERK 121 2675 SA_REP 148 8650 SH_CLERK 120 2900 SH_CLERK 124 2825 MK_MAN 100 13000 AD_PRES 24000 FI_MGR 101 12000 SA_REP 146 8500 SH_CLERK 123 3475 AD_ASST 101 4400 IT_PROG 102 9000 IT_PROG 103 4950 FI_ACCOUNT 108 7920 PU_MAN 100 11000 ST_CLERK 122 2700 SA_REP 145 8500 AC_ACCOUNT 205 8300 AD_VP 100 17000 DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- ST_CLERK 120 2625 ST_CLERK 124 2925 SA_REP 147 7766.66667 SA_REP 149 8333.33333 HR_REP 101 6500 PR_REP 101 10000 ST_CLERK 123 3000 SH_CLERK 121 3675 PU_CLERK 114 2780 SA_MAN 100 12200 MK_REP 201 6000 110 AC_ACCOUNT 8300 90 AD_VP 17000 50 ST_CLERK 2785 80 SA_REP 8396.55172 50 ST_MAN 7280 80 SA_MAN 12200 110 AC_MGR 12000 90 AD_PRES 24000 60 IT_PROG 5760 100 FI_MGR 12000 DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- 30 PU_CLERK 2780 50 SH_CLERK 3215 20 MK_MAN 13000 100 FI_ACCOUNT 7920 SA_REP 7000 70 PR_REP 10000 30 PU_MAN 11000 10 AD_ASST 4400 20 MK_REP 6000 40 HR_REP 6500 52 rows selected.

q2（UNION ALL）：
[c-sharp] view plain copy print ?
1. SELECT NULL department_id, job_id, manager_id, AVG (salary)
2. FROM employees
3. GROUP BY (job_id, manager_id)
4. UNION ALL
5. SELECT department_id, job_id, NULL manager_id, AVG (salary)
6. FROM employees
7. GROUP BY (department_id, job_id);
10. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
11. ------------- ---------- ---------- -----------
12. SH_CLERK 122 3200
13. AC_MGR 101 12000
14. ST_MAN 100 7280
15. ST_CLERK 121 2675
16. SA_REP 148 8650
17. SH_CLERK 120 2900
18. SH_CLERK 124 2825
19. MK_MAN 100 13000
20. AD_PRES 24000
21. FI_MGR 101 12000
22. SA_REP 146 8500
23. SH_CLERK 123 3475
24. AD_ASST 101 4400
25. IT_PROG 102 9000
26. IT_PROG 103 4950
27. FI_ACCOUNT 108 7920
28. PU_MAN 100 11000
29. ST_CLERK 122 2700
30. SA_REP 145 8500
31. AC_ACCOUNT 205 8300
32. AD_VP 100 17000
34. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
35. ------------- ---------- ---------- -----------
36. ST_CLERK 120 2625
37. ST_CLERK 124 2925
38. SA_REP 147 7766.66667
39. SA_REP 149 8333.33333
40. HR_REP 101 6500
41. PR_REP 101 10000
42. ST_CLERK 123 3000
43. SH_CLERK 121 3675
44. PU_CLERK 114 2780
45. SA_MAN 100 12200
46. MK_REP 201 6000
47. 110 AC_ACCOUNT 8300
48. 90 AD_VP 17000
49. 50 ST_CLERK 2785
50. 80 SA_REP 8396.55172
51. 50 ST_MAN 7280
52. 80 SA_MAN 12200
53. 110 AC_MGR 12000
54. 90 AD_PRES 24000
55. 60 IT_PROG 5760
56. 100 FI_MGR 12000
58. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
59. ------------- ---------- ---------- -----------
60. 30 PU_CLERK 2780
61. 50 SH_CLERK 3215
62. 20 MK_MAN 13000
63. 100 FI_ACCOUNT 7920
64. SA_REP 7000
65. 70 PR_REP 10000
66. 30 PU_MAN 11000
67. 10 AD_ASST 4400
68. 20 MK_REP 6000
69. 40 HR_REP 6500
71. 52 rows selected.
相关阅读:
bzoj 1176 cdq分治套树状数组
 Codeforces 669E cdq分治
 Codeforces 1101D 点分治
 Codeforces 1100E 拓扑排序
 Codeforces 1188D Make Equal DP
Codeforces 1188A 构造
 Codeforces 1188B 式子转化
 Codeforces 1188C DP 鸽巢原理
 Codeforces 1179D 树形DP 斜率优化
 git commit -m "XX"报错 pre -commit hook failed (add --no-verify to bypass)问题
原文地址：https://www.cnblogs.com/pekkle/p/6568775.html