時(shí)間：2015/6/28來源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評(píng)論(0)
使用SQL語句生成帶有小計(jì)合計(jì)的數(shù)據(jù)集:
測(cè)試用戶: scott
測(cè)試用表: dept,emp
//////////////////////////////////
//檢索出需要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)集
select dept.dname,emp.job,sal from emp,dept
where emp.deptno=dept.deptno;
//////////////////////////////////
//根據(jù)部門名稱以及職位進(jìn)行匯總,并為每個(gè)部門
生成'小計(jì)',最后生成'合計(jì)'.
select
decode(grouping(dept.dname),1,'合計(jì):',dept.dname)dname,
decode(grouping(emp.job)+grouping(dept.dname),1,'小計(jì):',emp.job)job,sum(sal) sum_sal from emp,dept where emp.deptno=dept.deptno group by rollup(dept.dname,emp.job);
運(yùn)行結(jié)果如下:
SQL> select dept.dname,emp.job,sal from emp,d
DNAME JOB SAL
-------------- --------- ----------
RESEARCH CLERK 800
SALES SALESMAN 1600
SALES SALESMAN 1250
RESEARCH MANAGER 2975
SALES SALESMAN 1250
SALES MANAGER 2850
ACCOUNTING MANAGER 2450
RESEARCH ANALYST 3000
ACCOUNTING PRESIDENT 5000
SALES SALESMAN 1500
RESEARCH CLERK 1100
DNAME JOB SAL
-------------- --------- ----------
SALES CLERK 950
RESEARCH ANALYST 3000
ACCOUNTING CLERK 1300
已選擇14行。
SQL> select
2 decode(grouping(dept.dname),1,'合計(jì):',de
3 decode(grouping(emp.job)+grouping(dept.d
ept where emp.deptno=dept.deptno group by rol
DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 小計(jì): 8750
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH 小計(jì): 10875
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES 小計(jì): 9400
合計(jì): 29025
已選擇13行。
SQL>
關(guān)鍵詞標(biāo)簽：SQL語句
相關(guān)閱讀
熱門文章
SqlServer2005對(duì)現(xiàn)有數(shù)據(jù)進(jìn)行分區(qū)具體步驟
sql server系統(tǒng)表?yè)p壞的解決方法
MS-SQL2005服務(wù)器登錄名、角色、數(shù)據(jù)庫(kù)用戶
Access、SQL Server、Oracle常見應(yīng)用的區(qū)別
人氣排行 如何遠(yuǎn)程備份（還原）SQL2000數(shù)據(jù)庫(kù)SQL2000數(shù)據(jù)庫(kù)遠(yuǎn)程導(dǎo)入（導(dǎo)出）數(shù)據(jù)配置和注冊(cè)O(shè)DBC數(shù)據(jù)源-odbc數(shù)據(jù)源配置教程SQL2000和SQL2005數(shù)據(jù)庫(kù)服務(wù)端口查看或修改SQL Server 2005降級(jí)到2000的正確操作步驟修改Sql Server唯一約束教程淺談JSP JDBC來連接SQL Server 2005的方法SQL Server創(chuàng)建表語句介紹