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不能被识别)