Oracle中用于类似MSSQL中top的关键字为rownumber,具体用法如下:
select firmcode,balance from (
select
rownum
rn,t.firmcode,t.balance
from FIRMBALANCE_TAB t
order by balance desc
) tab
where tab.rn >0 and tab.rn < 11;
注:tab指的是: select
rownum
rn,t.firmcode,t.balance
from FIRMBALANCE_TAB t
order by balance desc
查询得到的结果集.
tab.rn 指的是:select rownum rn
rownum是 oracle给出的一个用来表明当前记录位置的一个字段
下面的也是正确的:
select firmcode from FirmTable where rownum < 10 取前十条记录;
select firmcode from firmTable where rownum = 1 取第一条记录
但是下面是不正确的:
select firmcode from firmTable where rownum = 2 ,因为rownum都是从1开始的,没第一条不可能出来第二条的
另外,按oracle9i参考手册所言,如果跟order by的话需要用嵌套查询:
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
因为rownum是已经排序好的结果集中的行号,并且用在最上层的限制中.
select firmcode,balance from (
注:tab指的是: select
查询得到的结果集.
tab.rn 指的是:select rownum rn
rownum是 oracle给出的一个用来表明当前记录位置的一个字段
下面的也是正确的:
select firmcode from FirmTable where rownum < 10 取前十条记录;
select firmcode from firmTable where rownum = 1 取第一条记录
但是下面是不正确的:
select firmcode from firmTable where rownum = 2 ,因为rownum都是从1开始的,没第一条不可能出来第二条的
另外,按oracle9i参考手册所言,如果跟order by的话需要用嵌套查询:
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
因为rownum是已经排序好的结果集中的行号,并且用在最上层的限制中.