美文网首页
oracle使用(五)_子查询_分页rownum

oracle使用(五)_子查询_分页rownum

作者: 李moumou | 来源:发表于2021-11-08 23:06 被阅读0次

    sql允许多次嵌套,子查询即嵌套其他查询中得查询
    可把子查询返回结果理解成一张表,外层查询会把子查询返回的结果当成一张表
    子查询要用括号括起来
    将子查询放在比较运算符的右边,增强可读性

    子查询的分类:
    单行子查询:子查询返回一行记录,可使用单行比较运算符


    image.png

    多行子查询:子查询返回多行记录

    --有哪些雇员薪水在平均薪水之上
    --1、先求平均薪水
    select avg(nvl(e.sal,0)) from emp e;

    --2.把所有人的薪水和平均薪水作比较,使用的单行比较运算符
    select * from emp e where e.sal > (select avg(nvl(e.sal,0)) from emp e);

    -- 查询雇员表哪些人是经理人
    -- 1.先查询所有经理人 distinct去重(多行子查询)
    select distinct e.mgr from emp e;
    --2.在雇员表中过滤是经理的
    select * from emp where empno in (select distinct e.mgr from emp e);

    -- 每个部门的平均薪水等级
    --1.求出每个部门的平均薪水等级
    select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;
    --2.根据部门平均薪水断出薪水等级
    select g.deptno,sg.grade from salgrade sg join (select e.deptno,avg(nvl(e.sal,0)) avgsal from emp e group by e.deptno) g on g.avgsal between sg.losal and sg.hisal;

    --1.求平均薪水最高的部门的部门编号

    -- 求部门平均薪水
    select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;

    -- 求最高的薪水
    select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t;

    -- 求部门编号(重复的sql可以抽取为视图)
    select t.deptno,t.vsal from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t where t.vsal = (select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t);
    )

    --2.求部门的平均薪水的等级

    --3.求部门平均的薪水等级 (薪水等级的平均)
    --求每个人的薪水等级
    select e.deptno, sg.grade
    from emp e
    join salgrade sg
    on e.sal between sg.losal and sg.hisal;

    --求部门的平均薪水等级
    select t.deptno, avg(t.grade)
    from (select e.deptno, sg.grade
    from emp e
    join salgrade sg
    on e.sal between sg.losal and sg.hisal) t
    group by t.deptno;

    --4.求薪水最高的前5名员工

    -- 所有员工薪水降序
    select * from emp e order by e.sal desc;

    -- oracle中没有limit子句,rownum只存在于查询出来的虚拟表中
    select * from (select * from emp e order by e.sal desc) t where rownum <=5;

    --5.求薪水最高的第6到10名

    -- 默认对于基表 rownum,跟插入顺序有关
    select rownum rn,t.* from emp t;

    -- 对于子查询rownum跟内层查询的顺序有关
    --rn顺序是根据内层查询记录进行分配的
    select rownum rn,t.* from (select * from emp e order by e.sal desc) t;

    -- 根据rn顺序来过滤第6到10条
    select * from (select rownum rn,t.* from (select * from emp e order by e.sal desc) t) t1 where t1.rn >5 and t1.rn <=10;

    相关文章

      网友评论

          本文标题:oracle使用(五)_子查询_分页rownum

          本文链接:https://www.haomeiwen.com/subject/svlzaltx.html