Oracle数据库之子查询二

 --子查询的两种形式 1、子查询作为条件 where 2、子查询作为表 from 
 --分页SQL语句
 select e.*, rownum 序号 from emp e;
 select *
   from (select e.*, rownum 序号 from emp e)  p
  where p.序号 between 10 and 20;
 --伪列 rownum rowid  rownum动态的产生序号
 select *from emp  p where rownum  <6;
 select *from emp  p where rownum  >6;--大于号就不好使
 --rowid 静态的每个物理行编号
 select p.*,rowid ,rownum from emp p;
 --查询一下最高工资的那个人是谁？rownum实现
 select * from emp p where rownum=1 order by p.sal desc;
 select * from emp p where rownum=1 order by p.sal asc;
 ------TopN
 select *
   from (select * from emp p order by p.sal asc) emp
  where rownum <= 1;
  select *
   from (select * from emp p order by p.sal desc) emp
  where rownum <= 1;
  select *
   from (select * from emp p order by p.sal desc) emp
  where rownum <= 3;
  --子查询中的多行问题
  ---统计一下 雇员表中那些人的工资高于每个部门的平均工资
  select * from emp p where p.sal>(select avg(e.sal) from emp e);
  --返回多个行在单行子查询语句中是不可以的 
    --select * from emp p where p.sal>(select avg(e.sal) from emp e group by e.deptno);
    --方法一加max（）
   select * from emp p where p.sal>(select max(avg(e.sal)) from emp e group by e.deptno);
   --方法二 加 all all所有的 any任何一个
   select * from emp p where p.sal>all(select avg(e.sal) from emp e group by e.deptno);
   ---方法三 in 
   select *from emp p where p.deptno in('10','20');
   --统计一下谁换过工作
   select *from employees s where s.employee_id in(select empoyee_id from job_history);
--面试：exists和in的区别？ 完成功能是非常相似的
selct * from emp p where exists (select empoyee_id from job_history j where p.empoyee_id=j.empoyee_id);
--如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,
--反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists.
--其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists,那么以外层表为驱动表,先被访问.
--如果是IN,那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了,
--另外IN是不对NULL进行处理