背景
找到员工表中工资最高的前三名，可以通过排序得到降序排列，但是要只显示前三名只使用子查询和各种组函数还不行，所以引入了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,sal from
2 (select ename,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,sal from
2 (select rownum r1,sal from
3 (select ename,rownum r0,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 @H_403_252@file afiedt.buf
1 select e.ename,e.empno,e.sal,m.@H_403_252@avg
2 @H_403_252@from
3 (select deptno,@H_403_252@avg(sal) @H_403_252@avg
4 @H_403_252@from emp
5 group by deptno) m,emp e
6* where m.deptno=e.deptno and e.sal > m.@H_403_252@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,(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.
