ORACLE中的TOP-N查询（TOP-N分析），分页查询

转自：http://www.cnblogs.com/seven7seven/p/3662617.html

TOP-N查询（TOP-N分析）：就是获取某一数据集合中的前N条记录，实际应用中经常用到。

　　Oracle中不支持SELECT TOP语句（MySQL中也没用此语句），需要借助ROWNUM伪列来实现TOP-N查询。

　　ROWNUM伪列：是Oracle数据库对查询结果自动添加的一个伪列，编号从1开始，表示返回结果集的行号，它是在查询结果返回之前按照默认顺序分配的。ROWNUM在物理上（查询目标表中）并不存在，是每一次查询过程中动态生成的，所以称为“伪列”。因此，不允许以任何查询基表的名称做为前缀，连接查询中涉及多个物理表，但也只动态生成一个伪列。

1、非排序查询TOP-N分析

例题：返回员工信息表前5条记录

2、排序查询TOP-N分析

　　SELECT语句的执行顺序：先WHERE，后ORDER BY

例题：按照工资降序排序，查询工资最高的前5名员工的信息。

错误答案：

正确答案：

1. wher和order by，先执行where对数据筛选后，再去排序

2. select rownum,em.* from emp em order by em.sal;（oracle 执行顺序：rownum > order by）

结果为：

 

　　内层排序，外层设定范围（WHERE只能设定从1开始的范围），如rownum<=5；不能设定这样的范围，如rownum<=13 and rownum>=18，即

        【rownum 只可使用 < <=，不可使用> >=】。

　　子查询和主查询分别会产生各自的ROWNUM伪列，而此处用的是主查询自己的伪列。为了分析，请看下面的语句：

　　“原始行号”rn是子查询结果排序之前的行号（子查询执行了WHERE子句，但没有执行ORDER BY子句时的行号），结果如下：

 

如果不是很明白可以进一步参考

 

很显然，如果要使用原始行号（rn），按工资进行分页，是不正确的。

3、分页查询（应用极广）

　　分页显示的目的是控制输出结果集大小，将结果尽快返回。

例题：按照工资逆序、分页显示员工信息，每页显示6行记录。假定当前要显示第3页（即13~18行），应如何获取该页数据？

错误答案：该代码不会返回任何查询结果。

　　分析：ROWNUM自动编号从1开始。主查询执行时，取出子查询结果集中的第一条记录，并将其ROWNUM赋值为1，不符合WHERE限定的条件（13~18），于是这条记录被过滤掉。取出子查询结果集中的第二条记录，并将其ROWNUM赋值为1（因为前面没有找到过符合WHERE条件的记录），依次类推，永远也得不到符合条件的记录。（我们永远也无法绕过第一个，而直接去吃到第二个馒头）

正确答案：加一层嵌套查询，对子查询中的ROWNUM的值进行“固化”处理(使得rn是物理上存在的)，在主查询中即可对其进行任意范围的取值判断。

　　最内层排序（ORDER BY），中间层“固化”ROWNUM，最外层设定范围（WHERE中可以设定任意范围）。

　　

select * from (select rownum r,t1.* from (select * from emp order by sal desc) t1) t2 where t2.r between 6 and 10;

优化后：

正确写法：select * from (select rownum r,t1.* from (select * from emp order by sal desc) t1 where rownum<=10) t2 where t2.r>=6; （减少了t1表返回的数据，有优化作用）

错误写法：select * from (select rownum r,t1.* from (select * from emp order by sal desc) t1 wheret1.rownum<=10) t2 where t2.r>=6;（rownum是伪列，前面不能加表名修饰）

错误写法：select * from (select rownum r,t1.* from (select * from emp order by sal desc) t1 where r<=10) t2 where t2.r>=6;（r在where中，而where是最先执行的，之前并没有执行rownum r，所以导致where中的r不能被识别）