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

相关内容