Oracle数据库之子查询二

红太狼 2021-03-29 14:55 538阅读 0赞

--子查询的两种形式 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进行处理