Oracle 层次查询-学习笔记

层次查询

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

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

2 from emp e, emp d

3 where e.mgr=d.empno;

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

Oracle 层次查询-学习笔记

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

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 --按级别升序排序

示例：

Oracle 层次查询-学习笔记

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

Oracle 层次查询-学习笔记

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

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

Oracle 层次查询-学习笔记

练习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

Oracle 层次查询-学习笔记

相关推荐