Oracle 层次查询-学习笔记

层次查询

--自连接: 通过表的别名，将同一张表视为不同的表，然后再利用等值、不等值连接

自连接 SQL> selecte.ename||'的老板是'||d.ename

2 from emp e, emp d

3 where e.mgr=d.empno;

层次方式--打印树状结构，起始指明了根为KING的编号7839

层次查询：结决了自连接的性能问题

1 select level, empno, ename,sal, mgr -- level出现必须有connect by 子句

2 from emp -- prior 指前一次查询

3 connect by prior mgr=empno --连接条件【判断本次查询的mgr 是否等于 上次查询结界中的 empno】

4 start with empno=7369 --给定第一次查询条件

5 order by level --按级别升序排序

示例：

由于起始指定的mgr=7698产生了多个结果，所以第二级后就出现了重复

--精确显示时间间隔 【months_between函数】

select ename,sysdate-hiredate 天, (sysdate-hiredate)/7 星期, months_between(sysdate,hiredate) 月, months_between(sysdate,hiredate)/12 年 from emp

练习1找到员工表中工资最高的前三名， rownum的注意事项: SQL> 1. rownum一旦生成，就不变 SQL> 2. 对rownum只能使用<=号,不能 > SQL> select rownum,empno,ename,sal 2 from emp 3 where rownum<=3 4 order by sal desc;

统计每年入职的员工个数 1 select count(*) "Total", 2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0 )) "1980" 3 , sum(decode(to_char(hiredate,'yyyy'),'1981',1,0 )) "1981" 4 , sum(decode(to_char(hiredate,'yyyy'),'1982',1,0 )) "1982" 5 , sum(decode(to_char(hiredate,'yyyy'),'1987',1,0 )) "1987" 6 from emp SQL> / Total 1980 1981 1982 1987 ---------- ---------- ---------- ---------- ---------- 14 1 10 1 2

l 找到员工表中薪水大于本部 门平均薪水的员工。 SQL> select empno,ename,sal,avgsal 2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) a 3 where e.deptno=a.deptno and e.sal>a.avgsal;

分页1按sal排序后显示5到10(包含10)条记录【常用的】 select * from ( select rownum r, e1.* from ( select * from emp order by sal ) e1 where rownum <=10 ) where r>5

分页2按sal排序后显示5到10(包含10)条记录【高效的】 select * from emp where rowid in ( select rid --使用内层提供的rid 给外层 in 使用 from ( select rownum rn, rid --此 select 确定了 rownum 并提供给外层 rn from( select rowid rid --此select 确定了rowid 并提供给外层 rid from emp order by sal --确定最内层的rid 是按什么条件排序的 ) where rownum<=10 --使用rownum 确定上界 ) where rn>5 --使用内层提供的rn 确定下界 ) order by sal -- 由于rowid无序 必须外层再统一内层的排序 --rowid 是oracle为每张表提供的标示行的唯一不变id