子查询
范例1:查询公司之中工资最低的雇员的完整信息
SELECT MIN(sal) FROM emp ; |
SELECT * FROM emp WHERE sal=( SELECT MIN(sal) FROM emp) ; |
范例2:子查询返回的结果就当它是一个数字,即,直接判断此数字
SELECT * FROM emp WHERE sal=800 ; è 子查询返回的只是一个数字:800 |
范例3:查询出基本工资比ALLEN低的全部雇员信息
SELECT sal FROM emp WHERE ename='ALLEN' ; |
SELECT * FROM emp WHERE sal< ( SELECT sal FROM emp WHERE ename='ALLEN') ; |
范例4:查询基本工资高于公司平均薪金的全部雇员信息
SELECT AVG(sal) FROM emp ; |
SELECT * FROM emp WHERE sal>( SELECT AVG(sal) FROM emp) ; |
范例5:查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息
SELECT job FROM emp WHERE ename='ALLEN' |
SELECT sal FROM emp WHERE empno=7521 ; |
SELECT * FROM emp WHERE job=( SELECT job FROM emp WHERE ename='ALLEN') AND sal>( SELECT sal FROM emp WHERE empno=7521) ; |
范例6:查询与SCOTT从事同一工作且工资相同的雇员信息
SELECT job,sal FROM emp WHERE ename='SCOTT' ; |
SELECT * FROM emp WHERE (job,sal)=( SELECT job,sal FROM emp WHERE ename='SCOTT') ; |
SELECT * FROM emp WHERE (job,sal)=( SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT' ; |
范例7:查询与雇员7566从事同一工作且领导相同的全部雇员信息
SELECT job , mgr FROM emp WHERE empno=7566 ; |
SELECT * FROM emp WHERE (job,mgr)=( SELECT job , mgr FROM emp WHERE empno=7566) ; |
步骤三:增加一个过滤条件,不显示7566的信息
SELECT * FROM emp WHERE ( job,mgr)=( SELECT job,mgr FROM emp WHERE empno=7566) AND empno<>7566 ; |
范例8:查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息(包括ALLEN)
SELECT job, TO_CHAR(hiredate, 'yyyy') FROM emp WHERE ename='ALLEN' ; |
SELECT * FROM emp WHERE (job, TO_CHAR(hiredate, 'yyyy'))=( SELECT job, TO_CHAR(hiredate, 'yyyy') FROM emp WHERE ename='ALLEN') ; |
范例9:查询出与每个部门中最低工资相同的全部雇员信息
SELECT MIN(sal) FROM emp GROUP BY deptno ; |
SELECT * FROM emp WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; |
范例10:查询出不与每个部门中最低工资相同的全部雇员信息
SELECT * FROM emp WHERE sal NOT IN ( SELECT MIN(sal) FROM emp GROUP BY deptno) ; |
范例11:观察null对NOT IN操作的影响
SELECT e.ename FROM emp e WHERE e.empno NOT IN ( SELECT m.mgr FROM emp m) ; |
范例12:列出每个部门经理的工资
SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno ; |
范例13:使用=ANY操作符完成查询
SELECT * FROM emp WHERE sal=ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
范例14:使用>ANY操作符完成查询
SELECT * FROM emp WHERE sal >ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
范例15:使用<ANY操作符完成查询
SELECT * FROM emp WHERE sal <ANY ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
范例17:使用<>ALL操作符完成查询
SELECT * FROM emp WHERE sal <>ALL ( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno) ; |
范例18:验证exists结构
SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp WHERE empno=9999) ; |
范例19:验证exists结构
SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp) ; |
范例20:使用NOT EXISTS
SELECT * FROM emp WHERE NOT EXISTS( (SELECT * FROM emp WHERE empno=9999)); |
范例21:查询部门编号、雇员人数、平均工资,并且要求这些部门的平均工资高于公司平均薪金
SELECT AVG(sal) FROM emp ; |
SELECT deptno, COUNT(empno), AVG(sal) FROM emp GROUP BY deptno ; |
SELECT deptno, COUNT(empno), AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>( SELECT AVG(sal) FROM emp); |
范例22:查询出每个部门平均工资最高的部门名称及平均工资
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ; |
SELECT d.dname, ROUND(AVG(e.sal),2) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname HAVING AVG(sal)=( SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno) ; |
范例23:要求查询出每个部门的编号、名称、位置、部门人数、平均工资
SELECT * FROM dept ; |
SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno ; |
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d, (SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.dno(+) ; |
范例24:利用多字段分组
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) count,ROUND(AVG(sal),2) avg FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ; |
范例25:查询出所有在部门“SALES”(销售部)工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。
SELECT deptno FROM dept WHERE dname='SALES' ; |
SELECT empno ,ename ,sal ,comm ,job ,hiredate FROM emp WHERE deptno=( SELECT deptno FROM dept WHERE dname='SALES') ; |
SELECT e.empno, e.ename, e.sal, e.comm, e.job ,e.hiredate , temp.max ,temp.min FROM emp e , ( SELECT deptno dno , MAX(sal) max , MIN(sal) min FROM emp GROUP BY deptno) temp è 子查询负责统计信息,使用temp表示临时表的统计结果 WHERE e.deptno=( SELECT deptno FROM dept WHERE dname='SALES') AND e.deptno=temp.dno ; |
范例26:查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期,所在部门名称、位置,上级领导姓名,公司的工资等级,部门人数、平均工资、平均服务年限。
SELECT AVG(sal) FROM emp ; |
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate FROM emp e WHERE e.sal>( SELECT AVG(sal) FROM emp) ; |
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc FROM emp e , dept d WHERE e.sal>( SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno ; |
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc, m.ename mname FROM emp e , dept d , emp m WHERE e.sal>( SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+); |
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc, m.ename mname , s.grade FROM emp e , dept d , emp m ,salgrade s WHERE e.sal>( SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal ; |
SELECT e.empno, e.ename, e.sal, e.job, e.hiredate, d.dname, d.loc, m.ename mname , s.grade , temp.count, temp.avg , temp.avgyear FROM emp e , dept d , emp m ,salgrade s ,( SELECT deptno dno, COUNT(empno) count , ROUND(AVG(sal),2) avg , ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp GROUP BY deptno) temp WHERE e.sal>( SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal AND e.deptno=temp.dno; |
范例27:列出薪金比“ALLEN”或“CLARK”多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名,部门人数。
SELECT sal FROM emp WHERE ename IN ('ALLEN' , 'CLARK') ; |
SELECT e.empno, e.ename, e.sal FROM emp e WHERE e.sal >ANY( SELECT sal FROM emp WHERE ename IN ('ALLEN' , 'CLARK')) AND e.ename NOT IN('ALLEN','CLARK'); |
SELECT e.empno ,e.ename , e.sal , d.dname FROM emp e , dept d WHERE e.sal>ANY( SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK')) AND e.ename NOT IN('ALLEN','CLARK') AND e.deptno=d.deptno ; |
SELECT e.empno ,e.ename , e.sal , d.dname,m.ename mname FROM emp e , dept d , emp m WHERE e.sal>ANY( SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK')) AND e.ename NOT IN('ALLEN','CLARK') AND e.deptno=d.deptno AND e.mgr=m.empno(+); |
SELECT e.empno ,e.ename , e.sal , d.dname,m.ename mname,temp.count FROM emp e , dept d , emp m , ( SELECT deptno dno , COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.sal>ANY( SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK')) AND e.ename NOT IN('ALLEN','CLARK') AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.deptno=temp.dno ; |
范例28:列出公司各个部门的经理(假设每个部门只有一个经理,job为“MANAGER”)的姓名、薪金、部门名称、部门人数、部门平均工资。
SELECT ename, sal FROM emp WHERE job='MANAGER' ; |
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE job='MANAGER' AND e.deptno=d.deptno ; |
SELECT e.ename, e.sal, d.dname , temp.count , temp.avg FROM emp e, dept d , ( SELECT deptno dno , COUNT(empno) count , ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) temp WHERE job='MANAGER' AND e.deptno=d.deptno AND e.deptno=temp.dno ; |
范例29:查询出公司每个部门的编号、名称、位置、部门人数、平均工资
SELECT deptno , dname , loc , (SELECT COUNT(empno) FROM emp WHERE deptno=d.deptno) count , (SELECT AVG(sal) FROM emp WHERE deptno=d.deptno) avg FROM dept d ; |
范例30:使用WITH子句将emp表中的数据定义为临时表
WITH e AS ( SELECT * FROM emp) SELECT * FROM e ; |
范例31:查询每个部门的编号、名称、位置、部门平均工资、人数
WITH e AS ( SELECT deptno dno , ROUND(AVG(sal),2) avg , COUNT(sal) count FROM emp GROUP BY deptno) SELECT d.deptno,d.dname,d.loc,e.count,e.avg FROM e , dept d WHERE e.dno(+)=d.deptno ; |
范例32:查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称,显示的结果按照部门编号进行排序
WITH e AS ( SELECT deptno dno , MAX(sal) max FROM emp GROUP BY deptno) SELECT em.empno , em.ename , em.job , em.hiredate , em.sal , d.deptno,d.dname FROM e , emp em , dept d WHERE e.dno=em.deptno AND em.sal=e.max AND e.dno=d.deptno ORDER BY em.deptno ; |
范例26:使用PARTITION子句
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno) sum FROM emp ; |
范例27:不使用PARTITION进行分区,直接利用OVER子句操作
SELECT deptno , ename, sal , SUM(sal) OVER () sum FROM emp ; |
范例28:通过PARTITION设置多个分区字段
SELECT deptno , ename, sal , job , SUM(sal) OVER (PARTITION BY deptno , job) sum FROM emp ; |
范例29:观察ORDER BY子句
SELECT deptno , ename, sal , RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk FROM emp ; |
范例30:设置多个排序字段(sal和hiredate)
SELECT deptno , ename, sal , hiredate , RANK() OVER (PARTITION BY deptno ORDER BY sal , hiredate DESC) rk FROM emp ; |
范例31:直接利用ORDER BY排序所有数据
SELECT deptno , ename, sal , hiredate , SUM(sal) OVER (ORDER BY ename DESC) SUM FROM emp ; |
范例32:使用NULLS LAST
SELECT deptno , ename, sal , comm , RANK() OVER (ORDER BY comm DESC NULLS LAST) rk , SUM(sal) OVER (ORDER BY comm DESC NULLS LAST) SUM FROM emp ; |
范例33:在sal上设置偏移量
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sum FROM emp ; |
范例34:设置偏移量为300,采用下匹配方式处理
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum FROM emp ; |
范例35:匹配当前行数据
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) sum FROM emp ; |
范例36:使用UNBOUNDED不设置边界
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum FROM emp ; |
范例37:设置2行物理偏移
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sum FROM emp ; |
范例38:设置查询行范围
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum FROM emp ; |
范例39:查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、部门的人数、平均工资、最高工资、最低工资、总工资
SELECT * FROM ( SELECT empno,ename,job,sal,deptno , COUNT(empno) OVER (PARTITION BY deptno) count , ROUND(AVG(sal) OVER (PARTITION BY deptno)) avg , SUM(sal) OVER (PARTITION BY deptno) sum , MAX(sal) OVER (PARTITION BY deptno) max , MIN(sal) OVER (PARTITION BY deptno) min FROM emp ) temp WHERE temp.empno=7369 ; |
分析40:本程序由于需要针对于每一个部门找出整个部门的工资统计信息,所以可以利用分窗子句完成,而每一个分窗子句的范围应该是所有数据,所以可以使用“RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”作为范围限定。
SELECT e.empno , e.ename , e.sal , d.dname , d.loc , ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) avg_salary , MAX(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max_salary , MIN(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min_salary FROM emp e,dept d WHERE e.deptno=d.deptno ; |
范例41:观察RANK()和DENSE_RANK()函数
SELECT deptno,ename,sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) rank_result , DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dense_rank_result FROM emp ; |
范例42:使用ROW_NUMBER()函数
SELECT deptno,ename,sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) row_result_deptno , ROW_NUMBER() OVER (ORDER BY sal) row_result_all FROM emp ; |
范例43:查询每个部门的最高及最低工资
SELECT deptno, MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) max_salary , MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal DESC) min_salary FROM emp GROUP BY deptno ; |
范例44:验证FIRST_VALUE()与LAST_VALUE()函数
SELECT deptno , empno , ename , sal , FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_result , LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_result FROM emp WHERE deptno=10 ; |
范例45:观察LAG()与LEAD()函数
SELECT deptno , empno , ename , sal , LAG(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal ) lag_result , LEAD(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal ) lead_result FROM emp WHERE deptno=20 ; |
范例46:验证CUME_DIST()函数
SELECT deptno,ename,sal , CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal) cume FROM emp WHERE deptno IN (10,20) ; |
范例47:使用NTILE()函数
SELECT deptno , sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) sum_result , NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) ntile_result_a , NTILE(6) OVER (PARTITION BY deptno ORDER BY sal) ntile_result_b FROM emp ; |
范例48:计算各部门工资所占的总工资比率
SELECT deptno ,SUM(sal) , ROUND(RATIO_TO_REPORT(SUM(sal)) OVER () ,5) rate , ROUND(RATIO_TO_REPORT(SUM(sal)) OVER () ,5) * 100 || '%' precent FROM emp GROUP BY deptno; |
范例49:查询每个部门中各个职位的总工资
SELECT deptno , job , SUM(sal) FROM emp GROUP BY deptno , job ; |
SELECT deptno , SUM(DECODE(job, 'PRESIDENT' , sal , 0 )) PRESIDENT_JOB , SUM(DECODE(job, 'MANAGER' , sal , 0)) MANAGER_JOB , SUM(DECODE(job , 'ANALYST' , sal , 0 )) ANALYST_JOB , SUM(DECODE(job , 'CLERK' , sal, 0 )) CLERK_JOB , SUM(DECODE(job , 'SALESMAN' , sal , 0)) SALESMAN_JOB FROM emp GROUP BY deptno ; |
范例50:不使用DECODE()函数实现
SELECT temp.dno, SUM(president_job) , SUM(manager_job) , SUM(analyst_job) , SUM(clerk_job) , SUM(salesman_job) FROM ( SELECT deptno dno , (SELECT SUM(sal) FROM emp WHERE job='PRESIDENT' AND empno=e.empno) PRESIDENT_JOB , (SELECT SUM(sal) FROM emp WHERE job='MANAGER' AND empno=e.empno) MANAGER_JOB , (SELECT SUM(sal) FROM emp WHERE job='ANALYST' AND empno=e.empno) ANALYST_JOB , (SELECT SUM(sal) FROM emp WHERE job='CLERK' AND empno=e.empno) CLERK_JOB , (SELECT SUM(sal) FROM emp WHERE job='SALESMAN' AND empno=e.empno) SALESMAN_JOB FROM emp e ) temp GROUP BY temp.dno ORDER BY temp.dno DESC ; |
范例51:利用PIVOT函数实现转换
SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ; |
范例52:输出为XML
SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT XML ( SUM(sal) FOR job IN (ANY) ) ORDER BY deptno ; |
范例53:查询更多统计信息
SELECT * FROM ( SELECT job ,deptno , sal, SUM(sal) OVER(PARTITION BY deptno) sum_sal , MAX(sal) OVER(PARTITION BY deptno) max_sal , MIN(sal) OVER(PARTITION BY deptno) min_sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ; |
范例54:设置多个统计函数。查询出每个部门不同职位的总工资,和每个部门不同职位的最高工资
SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ; |
范例55:修改表结构及更新数据
ALTER TABLE emp ADD (sex VARCHAR2(10) DEFAULT '男') ; UPDATE emp SET sex='女' WHERE TO_CHAR(hiredate,'yyyy')='1981' ; COMMIT ; |
范例56:设置多个统计列
SELECT * FROM (SELECT deptno , job , sal , sex FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR (job, sex) IN ( ('MANAGER','男') AS manager_male_JOB , ('MANAGER','女') AS manager_female_JOB , ('CLERK','男') AS clerk_male_JOB , ('CLERK','女') AS clerk_female_JOB ) ) ORDER BY deptno ; |
范例57:验证UNPIVOT函数
WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB ) ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT ( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ; |
范例58:显示所有数据
WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB ) ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT INCLUDE NULLS( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ; |
范例59:观察分层的基本关系
SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ; |
范例60:利用“CONNECT_BY_ISLEAF”判断某一个节点是根节点还是叶子节点
SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ; |
范例61:使用“CONNECT_BY_ROOT”语句
SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , CONNECT_BY_ROOT ename FROM emp CONNECT BY PRIOR empno=mgr START WITH empno=7566 ; è 此处直接从7566数据开始列出 |
范例62:使用SYS_CONNECT_BY_PATH()函数取得节点路径信息
SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ; |
范例63:去掉某一节点
SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr AND empno!=7698 START WITH mgr IS NULL ; |
范例64:破坏程序结构的显示
SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER BY ename ; |
范例65:利用“ORDER SIBLINGS”保持层次关系
SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER siblings BY ename ; |
范例66:将KING的领导编号变为7698
UPDATE emp SET mgr=7698 WHERE empno=7839 ; |
范例67:判断循环
SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , ' 叶子节点') isleaf , DECODE(CONNECT_BY_ISCYCLE , 0 , '【√】没有循环' , 1 , '〖×〗存在循环') iscycle FROM emp CONNECT BY NOCYCLE PRIOR empno=mgr START WITH empno=7839 ORDER siblings BY ename ; |