- 掛牌上市企業
- 60秒人工響應
- 99.99%連通率
- 7*24h人工
- 故障100倍補償
Oracle 中 rollup、cube、grouping 使用詳解 |
| 發布時間: 2012/8/26 15:38:06 |
|
1.-- 使用Oracle 的樣列庫，演示 rollup, cube, grouping 的用法與使用場景 2. 3.--- ROLLUP ， 為了理解分組的成員數量，我增加了 分組的計數 COUNT(SAL) 4. SELECT E1.DEPTNO, 5. JOB, 6. TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'), 7. SUM (SAL), 8. COUNT (SAL) 9. FROM emp e1 10.GROUP BY ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE); 11. 12./* 13.分組情況為: 14.DEPTNO，job,HIREDATE 第 1 種分組 (A,B,C) 15.DEPTNO，job, 第 2 種分組 (A,B) 16.DEPTNO 第 3 種分組 (A) 17. 總分一個組 18. 19.-- 結果如下： 并進行部分數據解釋，以便讀者理解 20. DEPTNO JOB TO_CHAR(E1 SUM(SAL) COUNT(SAL) 21.---------- --------- ---------- ---------- ---------- 22. 10 CLERK 1982-01-23 1300 1 (A,B,C) -- 10號部門,CLERK工種，入職日期為1982-01-23，只有一個成員 工資和為：1300 23. 10 CLERK 1300 1 (A,B) -- 10號部門,CLERK工種，只有一個成員 工資和為：1300 24. 10 MANAGER 1981-06-09 2450 1 (A,B,C) -- 與第一條記錄相似的分析 25. 10 MANAGER 2450 1 (A,B) 26. 10 PRESIDENT 1981-11-17 5000 1 (A,B,C) 27. 10 PRESIDENT 5000 1 (A,B,C) 28. 10 8750 3 (A) -- 10號部門有3個成員，工資總計為8750 29. 20 CLERK 1980-12-17 800 1 30. 20 CLERK 800 1 31. 20 ANALYST 1981-12-03 3000 1 32. 20 ANALYST 3000 1 33. 20 MANAGER 1981-04-02 2975 1 34. 20 MANAGER 2975 1 35. 20 6775 3 36. 30 CLERK 1981-12-03 950 1 37. 30 CLERK 950 1 38. 30 MANAGER 1981-05-01 2850 1 39. 30 MANAGER 2850 1 40. 30 SALESMAN 1981-02-20 1600 1 41. 30 SALESMAN 1981-02-22 1250 1 42. 30 SALESMAN 1981-09-08 1500 1 43. 30 SALESMAN 1981-09-28 1250 1 44. 30 SALESMAN 5600 4 (A,B) -- 30號部門,SALESMAN工種，有4個成員 工資和為：5600 45. 30 9400 6 (A) -- 30號部 有6個成員， 工資總計為：9400 46. 24925 12 () -- 所有部門工資總和為：24925 47.*/ 48. 49. 50.--- CUBE ， 為了理解分組的成員數量，我增加了 分組的計數 COUNT(SAL) 51. SELECT E1.DEPTNO, 52. JOB, 53. TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'), 54. SUM (SAL), 55. COUNT (SAL) 56. FROM emp e1 57.GROUP BY CUBE (E1.DEPTNO, E1.JOB, E1.HIREDATE); 58. 59./* 60.分組原則： 61.GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對全表進行GROUP BY操作。 62. 63.-- 結果 ： 并進行部分數據解釋，以便讀者理解 64. DEPTNO JOB TO_CHAR(E1 SUM(SAL) COUNT(SAL) 65.---------- --------- ---------- ---------- ---------- 66. 24925 12 -- 全表分組，工資總合:24925 67. 1980-12-17 800 1 68. 1981-02-20 1600 1 69. 1981-02-22 1250 1 70. 1981-04-02 2975 1 71. 1981-05-01 2850 1 72. 1981-06-09 2450 1 73. 1981-09-08 1500 1 74. 1981-09-28 1250 1 75. 1981-11-17 5000 1 76. 1981-12-03 3950 2 (C) 1981-12-03 入職的有2位員工，工資總計3950 77. 1982-01-23 1300 1 78. CLERK 3050 3 (B) CLERK 工種，共計有3位員工，工資總計：3050 79. CLERK 1980-12-17 800 1 80. CLERK 1981-12-03 950 1 81. CLERK 1982-01-23 1300 1 82. ANALYST 3000 1 83. ANALYST 1981-12-03 3000 1 84. MANAGER 8275 3 85. MANAGER 1981-04-02 2975 1 86. MANAGER 1981-05-01 2850 1 87. MANAGER 1981-06-09 2450 1 88. SALESMAN 5600 4 89. SALESMAN 1981-02-20 1600 1 90. SALESMAN 1981-02-22 1250 1 91. SALESMAN 1981-09-08 1500 1 92. SALESMAN 1981-09-28 1250 1 93. PRESIDENT 5000 1 94. PRESIDENT 1981-11-17 5000 1 95. 10 8750 3 96. 10 1981-06-09 2450 1 97. 10 1981-11-17 5000 1 98. 10 1982-01-23 1300 1 99. 10 CLERK 1300 1 100. 10 CLERK 1982-01-23 1300 1 101. 10 MANAGER 2450 1 102. 10 MANAGER 1981-06-09 2450 1 103. 10 PRESIDENT 5000 1 104. 10 PRESIDENT 1981-11-17 5000 1 105. 20 6775 3 (A) -- 20號部 有3個成員， 工資總計為：6775 106. 20 1980-12-17 800 1 107. 20 1981-04-02 2975 1 108. 20 1981-12-03 3000 1 109. 20 CLERK 800 1 110. 20 CLERK 1980-12-17 800 1 111. 20 ANALYST 3000 1 112. 20 ANALYST 1981-12-03 3000 1 113. 20 MANAGER 2975 1 114. 20 MANAGER 1981-04-02 2975 1 115. 30 9400 6 (A) -- 30號部 有6個成員， 工資總計為：9400 116. 30 1981-02-20 1600 1 117. 30 1981-02-22 1250 1 118. 30 1981-05-01 2850 1 119. 30 1981-09-08 1500 1 120. 30 1981-09-28 1250 1 121. 30 1981-12-03 950 1 122. 30 CLERK 950 1 123. 30 CLERK 1981-12-03 950 1 124. 30 MANAGER 2850 1 125. 30 MANAGER 1981-05-01 2850 1 126. 30 SALESMAN 5600 4 (A、B) 30號部門， SALESMAN 工種，有4 個成員，工資總計：5600 127. 30 SALESMAN 1981-02-20 1600 1 128. 30 SALESMAN 1981-02-22 1250 1 129. 30 SALESMAN 1981-09-08 1500 1 (A、B、C) 0號部門， SALESMAN 工種，1981-09-08入職，1 個員工，工資總計：1500 130. 30 SALESMAN 1981-09-28 1250 1 (A、B、C) 0號部門， SALESMAN 工種，1981-09-28入職，1 個員工，工資總計：1250 131. 132.已選擇65行。 133.*/ 134. 135.--- GROUPING函數 136./* 137.GROUPING 是一個聚合函數,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算符添加行時，附加的列輸出值為1，當所添加的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。 138.僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯系的選擇列表中才允許分組。 139. 140.語法: GROUPING ( column_name ) 141. 142.是 GROUP BY 子句中用于檢查 CUBE 或 ROLLUP 空值的列。 143. 144.返回類型: int 145. 146.分組用于區分由 CUBE 和 ROLLUP 返回的空值和標準的空值。作為CUBE 或 ROLLUP 操作結果返回的 NULL 是 NULL 的特殊應用。它在結果集內作為列的占位符，意思是"全體"。 147.*/ 148. 149.-- grouping 樣列 150. SELECT E1.DEPTNO, 151. JOB, 152. TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD') HIREDATE, 153. SUM (SAL), 154. COUNT (SAL), 155. GROUPING (E1.DEPTNO) d, 156. GROUPING (JOB) j, 157. GROUPING (E1.HIREDATE) h 158. FROM emp e1 159.GROUP BY ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE); 160. 161. 162. 163./* 164.-- 結果 ： 并進行部分數據解釋，以便讀者理解 165. 166. DEPTNO JOB HIREDATE SUM(SAL) COUNT(SAL) D J H 167.---------- --------- ---------- ---------- ---------- --- --- --- 168. 10 CLERK 1982-01-23 1300 1 0 0 0 所有列都有數據作為分組，所以全為 0 169. 10 CLERK 1300 1 0 0 1 DEPTNO JOB列 有數據，而HIREDATE沒有數據，所以 H 列產生的值為：1 170. 10 MANAGER 1981-06-09 2450 1 0 0 0 171. 10 MANAGER 2450 1 0 0 1 172. 10 PRESIDENT 1981-11-17 5000 1 0 0 0 173. 10 PRESIDENT 5000 1 0 0 1 174. 10 8750 3 0 1 1 175. 20 CLERK 1980-12-17 800 1 0 0 0 176. 20 CLERK 800 1 0 0 1 177. 20 ANALYST 1981-12-03 3000 1 0 0 0 178. 20 ANALYST 3000 1 0 0 1 179. 20 MANAGER 1981-04-02 2975 1 0 0 0 180. 20 MANAGER 2975 1 0 0 1 181. 20 6775 3 0 1 1 182. 30 CLERK 1981-12-03 950 1 0 0 0 183. 30 CLERK 950 1 0 0 1 184. 30 MANAGER 1981-05-01 2850 1 0 0 0 185. 30 MANAGER 2850 1 0 0 1 186. 30 SALESMAN 1981-02-20 1600 1 0 0 0 187. 30 SALESMAN 1981-02-22 1250 1 0 0 0 188. 30 SALESMAN 1981-09-08 1500 1 0 0 0 189. 30 SALESMAN 1981-09-28 1250 1 0 0 0 190. 30 SALESMAN 5600 4 0 0 1 191. 30 9400 6 0 1 1 192. 24925 12 1 1 1 193.*/ 194. 195. 196.-- 應用 grouping 197.SELECT CASE 198. WHEN ( GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 0) 199. THEN DEPTNO|| ' '|| JOB|| ' ' || TO_CHAR (HIREDATE, 'YYYY-MM-DD')|| ' subtotal:' 200. WHEN ( GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 1) 201. THEN DEPTNO || ' ' || JOB || ' subtotal:' 202. WHEN ( GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1) 203. THEN DEPTNO || ' subtotal:' 204. WHEN ( GROUPING (E1.DEPTNO) = 1 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1) 205. THEN 'Total:' 206. END 207. "Total", 208. SUM (SAL), COUNT (SAL) 209. FROM emp e1 210.GROUP BY ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE); 211. 212.-- 結果如下： 213./* 214.Total SUM(SAL) COUNT(SAL) 215.------------------------------------ ---------- ---------- 216.10 CLERK 1982-01-23 subtotal: 1300 1 (A,B,C) 分組 217.10 CLERK subtotal: 1300 1 (A,B) 分組 218.10 MANAGER 1981-06-09 subtotal: 2450 1 219.10 MANAGER subtotal: 2450 1 220.10 PRESIDENT 1981-11-17 subtotal: 5000 1 221.10 PRESIDENT subtotal: 5000 1 222.10 subtotal: 8750 3 (A) 分組 10號部門，共3個成員，工資總計：8750 223.20 CLERK 1980-12-17 subtotal: 800 1 224.20 CLERK subtotal: 800 1 225.20 ANALYST 1981-12-03 subtotal: 3000 1 226.20 ANALYST subtotal: 3000 1 227.20 MANAGER 1981-04-02 subtotal: 2975 1 228.20 MANAGER subtotal: 2975 1 229.20 subtotal: 6775 3 230.30 CLERK 1981-12-03 subtotal: 950 1 231.30 CLERK subtotal: 950 1 232.30 MANAGER 1981-05-01 subtotal: 2850 1 233.30 MANAGER subtotal: 2850 1 234.30 SALESMAN 1981-02-20 subtotal: 1600 1 235.30 SALESMAN 1981-02-22 subtotal: 1250 1 236.30 SALESMAN 1981-09-08 subtotal: 1500 1 237.30 SALESMAN 1981-09-28 subtotal: 1250 1 238.30 SALESMAN subtotal: 5600 4 239.30 subtotal: 9400 6 240.Total: 24925 12 () 全部總計：12 個成員，工資總計為：24925 241. 242.已選擇25行。 243.*/ 本文出自：億恩科技【www.ibaoshan.net】 |
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
