oracle 学习笔记(三)

1.题目：找到薪水大于本部门平均薪水的员工。

select ename from emp e1 where sal>(select avg(sal) from emp e2  where e1.deptno=e2.deptno);

2.题目：找到员工表中工资最高的前三名的员工信息

思路：

1)先按照工资排序查询出数据 

2)再把查询出的数据当做一个表  

3)再查询这个表中的前三条数据

select * from (select sal from emp order by desc) where rownum<=3;

rownum是一个伪列，表示记录在结果集中的行号。
1，rownum一旦生成，就不会变化（会先按没有排序时的默认顺序生成rownum，然后再执行排序）。
2，对于rownum，只能使用<与<=，不能使用>与>=与=。

3.题目 查找出工资从低到高排除后的从5条记录到第10条记录。

思路：先查询出前10条，然后将rownum记录下来,这个时候r1 就可以使用>=了 

select rownum,empno,ename,sal 
from
(
select rownum r1,empno,ename,sal from 

(select empno,ename,sal from emp order by sal)

 where rownum<=10
)
where rownum<=10 and r1>5

rownum的详细说明：

rownum使用与分页
rownum说明：
rownum是个伪列，是随着结果集生成的，返回的第一行分配的是1，第二行是2等等，生成的结果是依次递加的，没有1就不会有2。
注意，不返回的就不算，第一条返回的结果的rownum为1。
rownum一旦生成，就不会变化了，所以要注意，指定排序时rownum就是打乱的了。
rownum的比较操作
我们说rownum不支持>, >=, =, between and，只支持<, <=等。
虽说不支持，但并不会报错，只是返回的数据为空，这是因为根本不能满足这样的where条件。
如where rownum>2：
取回第1条数据的rownum为1，不满足，就舍弃这条记录。
看下一条，然后取第2条数据的rownum还是为1，还是不满足，再舍弃。
以此类推，最终舍弃了所有的数据，这就是所谓不支持的原因。
于是我可以推出：
where rownum=1 是只有一条结果的（要求至少有一条结果）。
where rownum>=1 是返回所有结果的（要求至少有一条结果）。
where rownum<9 是有8条结果的（要求至少有8条结果）。
where rownum!=9 是有8条结果的（要求至少有8条结果）。
where rownum between 6 and 10 是没有结果的。
where rownum between 1 and 5 是有前5条结果（要求至少有5条结果）
实现rownum>n (n>0) 的效果：
rownum本身是不行的，所以就可以先查询出一个结果集合。
再从这个集合中查询，这时集合中的行号就是一个普通的字段了，可以做任何比较。
即使用子查询方法来解决。
注意问题：
1，查询时rownum可以直接使用，不要求一定要在select中出现，如：select id,name from user where rownum<=5;
2，在本查询中，where子句中要直接写rownum，前面不能加表别名，也不能使用使用select子句中为rownum定义的别名。
在order by子句中是可以使用为rownum定义的别名的。
3，这样的操作在大数据集中会影响速度。
分页：
（内层排序外层选，需要三层查询）
内：排序。
中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。
外：去掉前m条结果。