Oracle入门之rownum伪列--Top-N问题

2017-01-07 08:37:33来源:CSDN作者:lzjsqn人点击

背景

找到员工表中工资最高的前三名,可以通过排序得到降序排列,但是要只显示前三名只使用子查询和各种组函数还不行,所以引入了rownum的概念,用于解决排序问题里面,要单独处理,排好序的一部分数据的问题。

实质

  • rownum的生成按照oracle的默认顺序生成, 有它自己的生成规则
  • rownum 只能 where rownum <=3 where rownum <3,不能 where rownum >=3 where rownum > 3
  • 错误示例:
    select rownum, empno, ename, sal                from emp                where rownum >=3                order by sal desc                /                --- 没有符合条件的数据
  • 机制原因分析:
    • rownum 返回结果集合里元素的行号….
    • where rownum <=3 这样可以: 有了第一条,才有第二条;1 2 3
    • rownum >=3 : 第一条记录没有返回,就没有 rownum =2 的记录存在,rownum =2 记录若不存在,就没有第三条
    • 一个易犯错误是:
 select ename,rownum,sal,empno  2  from emp  3  where rownum <= 3  4  order by sal desc;ENAME          ROWNUM        SAL      EMPNO---------- ---------- ---------- ----------tom_abc         1       8000      1ALLEN           3       1600       7499SMITH           2        800       7369

因为在sqlplus(客户端)链接数据库(服务器)的时候,rownum就已经确定了,第一条记录占据了rownum=1,所以他是先逐行的为每条记录设置rownum,然后取出rownum<= 3的三行(rownum = 1、2、3),进行排序,所以不管如何排序,排序的对象仅仅是默认的前三行而已!由rownum的赋值过程决定的!

应用–解决Top-N问题

正确取出工资前三名高的员工信息

SQL> edWrote file afiedt.buf  1  select rownum,empno,ename,sal from  2  (select ename,rownum,sal,empno  3  from emp  4  order by sal desc)  5* where rownum < =3SQL> /    ROWNUM  EMPNO ENAME     SAL---------- ---------- ---------- ----------     1      1 tom_abc          8000     2   7839 KING         5000     3   7788 SCOTT        3000

oracle的分页—按照员工薪水,降序,求第二页数据

按照4行作为一页

SQL> edWrote file afiedt.buf  1  select r1,empno,ename,sal from  2  (select rownum r1,empno,ename,sal from  3  (select ename,rownum r0,sal,empno  4  from emp  5  order by sal desc)  6  where rownum < =8)  7* where r1 >= 5SQL> /    R1  EMPNO ENAME     SAL---------- ---------- ---------- ----------     5   7566 JONES        2975     6   7698 BLAKE        2850     7   7782 CLARK        2450     8   7499 ALLEN        1600

oracle分页思想总结

内层排序 外层选,需要三层查询解决m <= x <=n的问题:

  • 内:排序。
  • 中:使用rownum选择前n条;并给rownum指定一个别名,以供最外层过滤使用–为了使用>=操作符,毕竟rownum不可以使用rownum。
  • 外:去掉前m条结果。

找到 员工表 中 薪水 大于 本部门平均薪水 的员工

分析思路:

1 查找的是员工信息
2 大于 本部门平均薪水 ====> 求出 各个部门的平均薪水

select deptno, avg(sal) from emp                 group by deptno

3 本部门员工薪水大于本部门平均薪水 ===== sal > 平均薪水 条件,需要根据deptno相等进行比较!

  • 多表查询
SQL> edWrote file afiedt.buf  1  select e.ename,e.empno,e.sal,m.avg  2  from  3  (select deptno,avg(sal) avg  4  from emp  5  group by deptno) m,emp e  6* where m.deptno=e.deptno and e.sal > m.avgSQL> /ENAME       EMPNO        SAL    AVG---------- ---------- ---------- ----------tom_abc         1       8000     4187.5ALLEN        7499       1600 1566.66667JONES        7566       2975       2175BLAKE        7698       2850 1566.66667SCOTT        7788       3000       2175KING         7839       5000     4187.5FORD         7902       3000       21757 rows selected.
  • 子查询(相关子查询)

主查询中的参数和变量通过别名的方式传递给子查询:

SQL> edWrote file afiedt.buf  1  select ename,empno,sal,(select avg(sal) from emp where deptno=e.deptno) avg  2  from emp e  3* where sal > (select avg(sal) avg from emp where deptno=e.deptno)  4  /ENAME       EMPNO        SAL    AVG---------- ---------- ---------- ----------tom_abc         1       8000     4187.5ALLEN        7499       1600 1566.66667JONES        7566       2975       2175BLAKE        7698       2850 1566.66667SCOTT        7788       3000       2175KING         7839       5000     4187.5FORD         7902       3000       21757 rows selected.

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台