常常寫SQL語句的人應該知道Group by語句的主要使用方法是進行分類匯總,以下是一種它最常見的使用方法(依據部門、職位分別統計業績):
SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY a.dname,b.job; DNAME JOB SUM_SAL -------------- --------- ---------- SALES MANAGER 2850 SALES CLERK 950 SALES SALESMAN 5600 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING CLERK 1300 RESEARCH MANAGER 2975 RESEARCH ANALYST 6000 RESEARCH CLERK 1900這時候,假設有人跑過來跟你說:我除了以上數據之外,還要每一個部門總的業績以及全部部門加起來的業績,這時候你非常可能會想到例如以下的笨方法(union all):
select * from ( SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY a.dname,b.job UNION ALL --實現了部門的小計 SELECT a.dname,NULL, SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY a.dname UNION ALL --實現了全部部門總的合計 SELECT NULL,NULL, SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno) order by dname; DNAME JOB SUM_SAL -------------- --------- ---------- ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING 8750 RESEARCH CLERK 1900 RESEARCH MANAGER 2975 RESEARCH ANALYST 6000 RESEARCH 10875 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 SALES 9400 29025 union all 合并笨辦法產生的運行計劃 ------------------------------------------------------------------------------- Plan hash value: 2979078843 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 29 | 812 | 23 (22)| 00:00:01 | | 1 | SORT ORDER BY | | 29 | 812 | 23 (22)| 00:00:01 | | 2 | VIEW | | 29 | 812 | 22 (19)| 00:00:01 | | 3 | UNION-ALL | | | | | | | 4 | HASH GROUP BY | | 14 | 756 | 8 (25)| 00:00:01 | |* 5 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 | | 8 | HASH GROUP BY | | 14 | 672 | 8 (25)| 00:00:01 | |* 9 | HASH JOIN | | 14 | 672 | 7 (15)| 00:00:01 | | 10 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 | | 12 | SORT AGGREGATE | | 1 | 39 | | | |* 13 | HASH JOIN | | 14 | 546 | 7 (15)| 00:00:01 | | 14 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------事實上,假設你知道Group By的Rollup擴展的話,這樣的需求僅僅是小case:
SELECT a.dname,b.job, SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY ROLLUP(a.dname,b.job); DNAME JOB SUM_SAL -------------- --------- ---------- SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 SALES 9400 RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 RESEARCH 10875 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING 8750 29025 rollup寫法產生的運行計劃 ----------------------------------------------------------------------------- Plan hash value: 1037965942 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 756 | 8 (25)| 00:00:01 | | 1 | SORT GROUP BY ROLLUP| | 14 | 756 | 8 (25)| 00:00:01 | |* 2 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 | | 3 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 448 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------能夠發現,這樣的方法不但SQL書寫方便,性能也能得到提高。
這時候,假設又有人跑過來說:除了以上數據,他還須要每一個職位總的業績,你僅僅要把rollup換成cube就能夠了,例如以下所看到的:
-- CUBE分組 SELECT a.dname,b.job, SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY CUBE(a.dname,b.job); DNAME JOB SUM_SAL -------------- --------- ---------- 29025 CLERK 4150 ANALYST 6000 MANAGER 8275 SALESMAN 5600 PRESIDENT 5000 SALES 9400 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH 10875 RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 ACCOUNTING 8750 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000從上面能夠看出:cube比rollup的展現的粒度更細一些。
這時候,假設又有人跑過來說:他不須要那么細的數據,僅僅須要匯總的數據,能夠使用Grouping Sets:
---GROUPING SETS分組 SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal FROM dept a,emp b WHERE a.deptno = b.deptno GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job); HIRE DNAME JOB SUM_SAL ---- -------------- --------- ---------- 1987 4100 1980 800 1982 1300 1981 22825 ACCOUNTING 8750 RESEARCH 10875 SALES 9400 CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
