topN用法总结

一、查询前几条记录：

Select 列名1…列名n  from （select 列名1..列名n from 表名 order by 列名1..列名n） where  rownum<=N order by rownum asc;

二、在TOP N记录中抽出第M（M<=N）条记录

Select 列名1…列名n from ( select rownum recno, 列名1…列名n from (select 列名1…列名n from 表名 order by 列名1…列名n)  where rownum <=N ORDER BY ROWNUM ASC) where recno = M (M<=N)

三、抽出按某种方式排序的记录集中的第N条记录

Select 列名1…列名n from ( select rownum recno,列名1…列名n from (select 列名1…列名n from 表名 order by 列名1…列名n) where rownum <=N order by rownum asc) where recno=N;

四、抽出按某种方式排序的记录集中的第M条记录开始的X条记录

Select 列名1…列名n from ( select rownum recno,列名1…列名n from ( select 列名1…列名n from 表名 order by 列名1…列名n) where rownum <=N (N>=(M+X-1)) order by rownum asc) where recno between m and (m+x-1);

五、分页技术

Select * from ( select a.*,rownum rn from (select * from table_name) a where rownum <=40) where rn>=21;此语句应该比between 21 and 40效率高。