【文章內(nèi)容簡(jiǎn)介】 1ROLLUP只用復(fù)合列例如group by rollup(a,b,c)的操作結(jié)果等同于group by(a,b,c),group by(a,b),group by(a)和group by()的并集select deptno,job,sum(sal) from emp group by rollup((deptno,job))。 DEPTNO JOB SUM(SAL) 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 CLERK 3210 20 ANALYST 3300 20 MANAGER 30 2000 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 CUBE中使用復(fù)合列select deptno,job,avg(sal) from emp group by cube((deptno,job))。 DEPTNO JOB AVG(SAL) 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 CLERK 1605 20 ANALYST 3300 20 MANAGER 30 2000 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 使用GROUPING SETS操作符合并多個(gè)分組的統(tǒng)計(jì)結(jié)果顯示部門平均工資： select deptno,avg(sal) from emp group by deptno。 DEPTNO AVG(SAL) 10 20 30 顯示崗位平均工資： select job,avg(sal) from emp group by job。 JOB AVG(SAL) ANALYST 3300 CLERK 1365 MANAGER PRESIDENT 5000 SALESMAN 1400 2000 顯示部門和崗位平均工資： select deptno,job,avg(sal) from emp group by grouping sets(deptno,job)。 DEPTNO JOB AVG(SAL) 10 20 30 ANALYST 3300 CLERK 1365 MANAGER PRESIDENT 5000 SALESMAN 1400 2000連接分組連接多個(gè)grouping sets select deptno,job,mgr,sum(sal) from emp group by grouping sets(deptno),grouping sets(job,mgr)。DEPTNO JOB MGR SUM(SAL) 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 CLERK 3210 20 ANALYST 3300 20 MANAGER 30 2000 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 10 5000 10 7782 1300 10 7839 2450 20 7566 5300 20 7788 1210 20 7839 30 2000 30 7698 6550 30 7839 2850連接rollup select deptno,job,mgr,sum(sal) from emp group by deptno,rollup(job,mgr)。 DEPTNO JOB MGR SUM(SAL) 10 CLERK 7782 1300 10 CLERK 1300 10 MANAGER 7839 2450 10 MANAGER 2450 10 PRESIDENT 5000 10 PRESIDENT 5000 10 8750 20 CLERK 7566 2000 20 CLERK 7788 1210 20 CLERK 3210 20 ANALYST 7566 3300 20 ANALYST 3300 20 MANAGER 7839 20 MANAGER 20 30 2000 30 2000 30 CLERK 7698 950 30 CLERK 950 30 MANAGER 7839 2850 30 MANAGER 2850 30 SALESMAN 7698 5600 30 SALESMAN 5600 30 11400連接CUBE select deptno,job,mgr,sum(sal) from emp group by deptno,cube(job,mgr)。 DEPTNO JOB MGR SUM(SAL) 10 5000 10 8750 10 7782 1300 10 7839 2450 10 CLERK 1300 10 CLERK 7782 1300 10 MANAGER 2450 10 MANAGER 7839 2450 10 PRESIDENT 5000 10 PRESIDENT 5000 20 20 7566 5300 20 7788 1210 20 7839 20 CLERK 3210 20 CLERK 7566 2000 20 CLERK 7788 1210 20 ANALYST 3300 20 ANALYST 7566 3300 20 MANAGER 20 MANAGER 7839 30 2000 30 2000 30 2000 30 11400 30 7698 6550 30 7839 2850 30 CLERK 950 30 CLERK 7698 950 30 MANAGER 285030 MANAGER 7839 2850 30 SALESMAN 5600 30 SALESMAN 7698 5600第七章 子查詢單行子查詢：只返回一行數(shù)據(jù)的子查詢語(yǔ)句 select ename,sal,deptno from emp where deptno= (select deptno from emp where ename=39。SCOTT39。) AND ename39。SCOTT39。多行子查詢，IN,ALL,ANY IN處理匹配子查詢?nèi)我粋€(gè)值的行select ename,job,sal,deptno from emp where job IN (select distinct job from emp where deptno=10)。 ALL必須匹配所有子查詢結(jié)果，不能單獨(dú)使用sele