Oracle,Top-N 查询,分页查询.doc

1、在ORACLE中实现SELECT TOP N 由于ORACLE不支持SELECT TOP语句，所以在ORACLE中经常是用order by跟rownum的组合来实现SELECT TOP N的查询。 实现方法如下所示： SELECT列名列名FROM （ SELECT列名列名FROM 表名 order by 列名列名 ） WHERE rownum= N / 抽出记录数 order by rownum asc； 例题： 顾客表customer(id,name)有如下数据： ID NAME 01 first 02 Second 03 third 04 forth 05 fifth 06 sixth 07 seventh 08 eighth 09 ninth 则按NAME的字母顺序抽出前三个顾客的SQL语句如下所示： SELECT * FROM ( SELECT * FROM CUSTOMER order by NAME ) WHERE rownum= 3 order by rownum ASC； 输出结果为： ID NAME 08 eighth 05 fifth 01 first 2、抽出按某种方式排序的记录集中的第N条记录rownum是记录表中数据编号的一个隐藏子段，所以在得到TOP N条记录的时候同时抽出记录的rownum，然后再从这N条记录中抽取最后一条记录。 实现方法如下所示： SELECT 列名列名 FROM ( SELECT rownum RECNO, 列名列名 FROM ( SELECT 列名列名 FROM 表名 ORDER BY 列名列名 ) WHERE rownum= N / 抽出记录数 order by rownum ASC ) WHERE RECNO = N；例题：同样以上表的数据为基础，得到以NAME的字母排序的第二个顾客的信息的SQL语句： SELECT ID, NAME FROM ( SELECT rownum RECNO, ID, NAME FROM ( SELECT * FROM CUSTOMER order by NAME ) where rownum= (M + X - 1)这个范围内，当然最后的抽取条件应该是 RECNO between M and (M + X - 1) 之间。 SELECT 列名列名 FROM ( SELECT rownum RECNO, 列名列名 FROM ( SELECT 列名列名 FROM 表名 order by 列名列名) WHERE rownum = N / N = (M + X - 1) order by rownum ASC ) WHERE RECNO between M and (M + X - 1)；例题： 同样以上面的数据为例，抽取NAME的字母顺序的第2条记录开始的3条记录： SELECT ID, NAME FROM ( SELECT rownum RECNO, ID, NAME FROM (SELECT * FROM CUSTOMER order by NAME ) WHERE rownum = (2+3-1) order by rownum ASC ) WHERE RECNO between 2 and (2+3-1)； 结果如下： ID NAME 05 fifth 01 first 04 forth 以此为基础，再扩展的话，做成存储过程，将开始记录数以及抽取记录数作为参数，就可以轻松实现分页抽取数据。Oracle分页查询1. 利用分析函数row_number() over ( partition by col1 order by col2 )比如想取出100-150条记录,按照tname排序select tname,tabtype from ( select tname,tabtype,row_number() over ( order by tname ) rn from tab)where rn between 100 and 150; 2. 直接使用rownum 虚列 select tname,tabtype from ( select tname,tabtype,rownum rn from tab where rownum = 100; 使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序。经过测试，在100万条数据的表中,检索数据的时候,方法2的速度要比方法1要快的. = 在构建自定义搜索引擎时，开发人员常遇到的一个问题是实现某种类型的分页功能；也就是说，允许用户提交一个返回很多行数据的查询，但是只显示前20条。在用户点击一个链接时，下20条或者前20条数据会从数据库应用程序中取出。数据库访问存在的一个问题是来自 Web 站点的请求是无状态的。在等待用户请求下一组数据时，让数据库维护一个游标的效率是非常低的。对 HTML 编写代码让游标位于客户端是可以做到的，但是游标是一个有限的资源，所以每次在页面装载完成时最好关闭游标。Oracle 游标不支持通过行集（rowset）向后移动；但是用户总是会在浏览器中后退，或者不按顺序请求一个行集。显然，返回有限行数据就成了数据库服务器的责任。伪列（pseudocolumn）ROWNUM 中包含有当前的行号。很多人在第一次试图返回表中中间某段记录子集时，都会发现下面这种方法行不通：select * from all_objects where rownum between 30 and 49; 这种写法之所以行不通，是因为 ROWNUM 列只有在记录被取出或过滤时才会应用到记录上。第一行在被取出时会被抛出，因为它的 ROWNUM 是1。然后，下一行被取出；它也会被抛出，因为它是新的“1”，以此类推，直到所有的行都被使用。这个查询不会返回任何记录。解决方法是在看到30到50之间的记录时必须先取出1到30行的记录：select * from all_objects where rownum = 49; 然后，你可以将它作为一个子查询并过滤掉开始点之前的所有记录（注意我必须为“rownum”提供一个别名才能编译）：select * from( select rownumr,all_objects.* from all_objects where rownum = 30;为了保证它的效率，不妨对这个限制使用绑定变量。这将使用所有此类请求在字面上完全相同，从而消除了在每次请求不同的范围求时重新解析查询：select * from ( select rownumr,all_objects.* from all_objects where rownum = :max;如果你使用的语言能够通过存储过程返回行集，那么数据库将自动在内部处理绑定变量。然后，应用程序代码就会只接收它请求的记录，而不必决定接收哪些记录。create or replace procedure search( p_mininteger, p_maxinteger,p_rowset out sys_refcursor )isbegin select cursor(*) into p_rowset from ( select rownumr,all_objects.* from