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

背景

找到员工表中工资最高的前三名，可以通过排序得到降序排列，但是要只显示前三名只使用子查询和各种组函数还不行，所以引入了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      1
ALLEN           3       1600       7499
SMITH           2        800       7369

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

应用–解决Top-N问题

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

SQL> ed
Wrote 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 < =3
SQL> /

    ROWNUM  EMPNO ENAME     SAL
---------- ---------- ---------- ----------
     1      1 tom_abc          8000
     2   7839 KING         5000
     3   7788 SCOTT        3000

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

按照4行作为一页

SQL> ed
Wrote 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 >= 5
SQL> /

    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> ed
Wrote 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.avg
SQL> /

ENAME       EMPNO        SAL    AVG
---------- ---------- ---------- ----------
tom_abc         1       8000     4187.5
ALLEN        7499       1600 1566.66667
JONES        7566       2975       2175
BLAKE        7698       2850 1566.66667
SCOTT        7788       3000       2175
KING         7839       5000     4187.5
FORD         7902       3000       2175

7 rows selected.

• 子查询（相关子查询）

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

SQL> ed
Wrote 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.5
ALLEN        7499       1600 1566.66667
JONES        7566       2975       2175
BLAKE        7698       2850 1566.66667
SCOTT        7788       3000       2175
KING         7839       5000     4187.5
FORD         7902       3000       2175

7 rows selected.