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 <= 150
)
where rn >= 100;


注释：使用序列时不能基于整个记录集合来进行排序,假如指定了order by子句,排序的的是选出来的记录集的排序。


在ORACLE如果想取一张表按时间排序后的前5条最新记录: 方法一\二对,方法三错

SELECT  GUID,title,content FROM

(SELECT  GUID,title,content, row_number() over (order by releasetime desc)tm

FROM web_LO_Article WHERE funID=20 and content like '%<img %')

WHERE tm between 1 and 5



或者:select * from (select * from  web_LO_Article where funID=20 and content like '%<img %' order by releasetime desc)where rownum<6



ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID)



SELECT  GUID,title,content,releaseTime
FROM web_LO_Article

WHERE funID=20 and rownum<6 and content like '%<img %'  order by releaseTime desc



方法三表示:查询数据库中的前5条数据，然后在对它们按时间降序排列。

select * from (select row_number() over(order by id) rn from et_sys_treebase) where rn between 3 and 5
表的记录就可以查询出来，结果是从3到5的记录