SELECT TOP N 问题! (100分)

原标题:怎样在Oracle中实现类似MS SQL Server中的 TOP(返回结果集的前n行)功能?
这是两个关于Oracle的问题,希望高手帮忙: (版本:Oracle 8.0.5.0.0)
1.怎样在Oracle中实现类似MS SQL Server中的 TOP(返回结果集的前n行)功能?
即如何在Oracle中的Select语句中返回结果集的前n行,有类似MS SQL 的TOP语句吗?
2.怎样在Oracle中的Select语句中为每条记录加一个递增的编号? 和MS SQL中的功能类似,只在显示的时候有这个编号.
下面是关于本问题的解答:
SELECT TOP N 问题

下面的内容是我从网上搜集的, 我把它整理出来以帮助大家解决类似的问题.

简单地说,TOP N问题就是:在SELECT中,仅选择按照某(些)列排序后TOP N的记录. 考虑到等值问题,又可以分为两种: 一是仅仅返回N条记录, 二是还包括所有于第N条等值的记录(M2). 当然最内层的子查询也可以有其他的子句, 或者TOP N也可以应用在没有ORDER BY的情况下,这样更简单.

本文章只讨论ORACLE 8i,SQL SERVER 7,DB2 UDB 7和MySQL.

********************************************************************************************
1. ORACLE 8i: 用ROWNUM<=N,使用子查询
注: 在Oracle 8i以前的版本中(如8.0.5),子查询中不支持Order By子句,虽然用Group By也能实现排序功能,但降序怎么办? 我还没解决.

含义解释：
(1)、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，
　 依此类推，这个伪字段可以用于限制查询返回的总行数。
(2)、rownum不能以任何基表的名称作为前缀。

M1: 返回N行记录
SELECT * FROM
(SELECT * FROM MYTABLE ORDER BY ORD_COL DESC)
WHERE ROWNUM<=N

M2:
SELECT * FROM MYTABLE WHERE ORD_COL>=
(SELECT MIN(ORD_COL) FROM
(SELECT * FROM MYTABLE ORDER BY ORD_COL DESC)
WHERE ROWNUM<=N)
ORDER BY ORD_COL DESC

M3: 可以用在限制返回记录条数的地方，保证不出错，如：隐式游标
Select rownum,* from MyTable where rownum=1

M4: 1以上都查不到记录,没有查到记录
Select rownum,* from MyTable where rownum=2

M5:由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录
Select rownum,* from MyTable where rownum>5;

M6:用rownum实现大于、小于逻辑(返回rownum在4—10之间的数据,用minus操作，速度会受影响)
Select rownum,* from MyTable where rownum<10
minus
Select rownum,* from MyTable where rownum<5
或者
Select * from (Select rownum row_id ,* from
(select * from MyTable group by ORD_Col))
where row_id between 5 and 9

注意以下的错误用法:
SELECT * FROM MYTABLE
WHERE ROWID<=N
ORDER BY ORD_COL DESC;
(因为WHERE ROWNUM<=N 在ORDER BY前执行)
可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的

如果想为记录增加一个序列号,可以用如下用法:
select rownum,month,sell from
(select month,sell from sale group by month,sell(或order by sell))
where rownum<13;

*****************************************************************************************
2.SQL SERVER 7:
用TOP N (WITH TIES)
M1:
SELECT TOP N * FROM MYTABLE ORDER BY ORD_COL;

M2:
SELECT TOP N WITH TIES * FROM MYTABLE ORDER BY ORD_COL;
注: SQL SERVER 7提供了PERCENT N WITH TIES, ACCESS中提供了TOP N,但含义是M2.

****************************************************************************************
3: DB2:
用FETCH FIRST N ROWS ONLY

M1:
SELECT * FROM MYTABLE
ORDER BY ORD_COL DESC
FETCH FIRST N ROWS ONLY

M2:
由于DB2的子查询不支持ORDER BY子句,因此无法构造. 实际上,DB2提供了一组新的函数,称为OLAP FUNCTIONS, 可以很好的支持TOP N问题.
SELECT * FROM
(SELECT MYKEY,RANK() OVER (ORDER BY ORD_COL DESC) AS MYRANK FROM MYTABLE) AS RANKED_TABLE
WHERE MYRANK<=N

同样,利用OLAP函数,M1又可以表示为:
SELECT * FROM
(SELECT MYKEY,ROW_NUMBER() OVER (ORDER BY ORD_COL DESC) AS MYRANK FROM MYTABLE) AS RANKED_TABLE
WHERE MYRANK<=N

M3:
除了M1,M2两种TOP N外,还有一种被DB2称为DENSE_RANK,通过下例分别说明.
M1: TOP 3 -- 1(4,101), 2(5,101), 3(1,100) 或者1(4,101), 2(5,101), 3(2,100)
M2: TOP 3 -- 1(4,101), 1(5,101), 3(1,100), 3(2,100)
M3: TOP 3 -- 1(4,101), 1(5,101), 2(1,100), 2(2,100), 3(3,99)

M3如此实现:
SELECT * FROM
(SELECT MYKEY,DENSE_RANK() OVER (ORDER BY ORD_COL DESC) AS MYRANK
FROM MYTABLE) AS RANKED_TABLE
WHERE MYRANK<=N

M4:
为避免DBMSs的方言问题,M2,M3可以有如下标准(SQL92)表示:
--M2: 注意: 两个比较符都是 >
SELECT * FROM MYTABLE M1 WHERE N>
(SELECT COUNT(*) FROM MYTABLE M2 WHERE M2.ORD_COL>M1.ORD_COL)

--M3: 注意:与M2的不同在于DISTINCT的使用
SELECT * FROM MYTABLE M1 WHERE N>
(SELECT COUNT(DISTINCT M2.ORD_COL) FROM MYTABLE M2 WHERE M2.ORD_COL>M1.ORD_COL)

至于M1, 我目前还没有想到合适的标准写法,请补充.

***************************************************************************************
4. 关于MYSQL
相对于各主流RDBMS来说,MYSQL要简单,幼稚地多.在运行MYSQL时发现居然不支持子查询. 因此MYSQL中的TOP N问题要复杂一些.
SELECT * FROM MYTABLE, MYTABLE M2
WHERE M2.ORD_COL>=M1.ORD_COL
GROUP BY M1.MYKEY
HAVING COUNT(M2.ORD_COL) <=N
但是,这个语句是有问题的. 一个极端的例子:当所有的ORD_COL的数值都一样时. 如果哪位想到了如何解决这个问题,请补充.

请Oracle高手请帮一下忙!

我在线等待大家的回复....

用rownum就可以了，比如
strselect="select * from fee_detail where rownum<1500"

要自动递增的话，创建一个序列不就行了

RowNum同时可以实现这两个功能

第一个问题按haidan的方法(用rownum)已经解决.
to haidan: 等第二个问题完成后我马上发分;

to shuizi2000: 怎样创建一个自动递增序列? 能否详细一些?

to desertsmoke:
怎样用RowNum实现第二个功能? 我原来用MS SQL,知道可以,到了Oracle下,好多都不一样了!
所以请大家一定说详细一点,最好能有示范SQL语句. 谢谢!

你在表中存一个序列值不行吗？例如：
Create Sequence CustomerID increment by 1 start with 1000;
Insert Into CUSTOMER(Name, Contact, ID) Values('COLE CONSTRUCTION', 'VERONICA',
CustomerID.NextVal)
如果插入的是第一条，则用CustomerID.CurrVal，不知是否符合你要求？

to shuizi2000:
表已经存在,并且没有序列值的字段,我只是想在用Select已经查询显示的时候,自动加上这个字段,
能够明确的知道当前的记录是结果集中第几条; 不想在数据库中增加一字段,再说增加字段也不能解决问题,
因为返回的结果集是动态的.

www.oradb.net 里关于rownum的使用有很详细的说明

一般用法：
select * from (select rownum rn,* from table_a order by id desc) where rn<1500

错误的写法：
select * from table_a where rn<1500 order by id desc
这个sql语句取出来的并不是按照“id”排序的数据，而是入库顺序，可以自己试一
下就知道rownum的用法了

select rank() over(order by b.rownum) as rm, b.*,
from
(
select rownum, * from
table
) b
where rm > 10 and rm <20

正如backlove所说,我研究一下;

To backlove:
在"select * from (select rownum rn,* from table_a order by id desc) where rn<1500"
的子查询中只能用Group by,不能用Order by(在我这儿是这样,不知其他版本如何?); 如果我要由大到小
排列记录(降序),如何实现?

为什么我这儿子查询中不能用Order by,只能用Group by?

用order by 后就不能用 where rownum<

可是我既需要rownum,又需要降序排列记录,怎么办?
我见网上的文章里可以以上二者兼得.
是不是和我的Oracle版本(8.0.5.0.0)有关? 用Oracle 8i 是不是可以?

upgrade to 8i, there are lots of new analytical SQL function.

问题完成了!
我把Oracle升级到了8.16,子查询也可以用Order By了(看来,正版的不是什么好东西^_^).一切OK! 谢谢大家!

我把关于这次问题从网上搜集的资料整理出来放在楼顶,以备参考.