SELECT TOP N 问题

时　间:2008-04-18 08:03:36
作　者:UMVSOFT整理   ID：16  城市：江阴
摘　要:SELECT TOP N 问题
正　文:

 本贴讨论Select TOP N问题.
    现在正在一家计算机公司兼职,开发ASP应用. 在最近的一个商业项目里有这样一个需求:用户在查询时,只需要按照某(些)列排序后TOP 20的纪录. SQL SERVER 7很好地支持了TOP N 操作,但考虑到系统移植问题, 我又考虑在其他几个主流DBMSs中如何实现. 鉴于只有DB2 UDB 7,ORACLE 8i,SQL SERVER 7,本贴仅讨论这三个DBMS.
    简单地说,TOP N问题就是:在Select中,仅选择按照某(些)列排序后TOP N的纪录. 考虑到等值问题,又可以分为两种: 一是仅仅返回N条纪录(M 1), 二是还包括所有于第N条等值的纪录(M 2). 当然最内层的子查询也可以有其他的子句, 或者TOP N也可以应用在没有ORDER BY的情况下,这样更简单.

1. 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,但含义是M 2.

2. orACLE 8i: 用 ROWNUM<=N

M1:
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
注意以下两种错误用法:
WRONG 1:
Select * FROM MYTABLE
Where  ROWID<=N
orDER BY orD_COL DESC;

WRONG 2:(因为Where ROWNUM<=N 在ORDER BY 前执行)
Select * FROM MYTABLE
Where  ROWNUM<=N
orDER BY orD_COL DESC;

3: DB2
用FETCH FIRST N ROWS ONLY
M1:
Select * FROM MYTABLE
orDER BY orD_COL DESC
FETCH FIRST N ROWS ONLY
M2:
没有找到,因为DB2不允许在FROM中嵌套有ORDER BY子句的子查询.

还不清楚ORACLE的M 2有没有更好的办法,以及其他的DBMS如何实现TOP N操作,请其他朋友补充.
----------------------------------------------------------------------------------
各语句已经测试过, EXAMPLE:
create table mytable(mykey int, ord_col int);

 

 

insert into mytable values(1, 100);
insert into mytable values(2, 100);
insert into mytable values(3, 99);
insert into mytable values(4, 101);
insert into mytable values(5, 101);

今天继续讨论Select TOP N问题.
1. DB2的M 2
在前一篇讨论中提到,由于DB2的SUBQUERY不支持ORDER BY子句,因此无法构造M2. 实际上,DB2提供了一组新的函数,称为 OLAP FUNCTIONS, 可以很好的支持TOP N问题.
M2:
Select * FROM
   (    Select MYKEY,RANK() OVER (ORDER BY orD_COL DESC) AS MYRANK
        FROM MYTABLE ) AS RANKED_TABLE
Where MYRANK<=N

利用OLAP函数,除了上一篇中所提方法,DB2的M1又可以表示为:
Select * FROM
   (    Select MYKEY,ROW_NUMBER() OVER (ORDER BY orD_COL DESC) AS MYRANK
        FROM MYTABLE ) AS RANKED_TABLE
计算机教程Select TOP N 问题来自www.itwen.comIT WEN计算机教程网

Where MYRANK<=N

2. 除了M1, M2两种TOP N外,还有一种被DB2称为 DENSE_RANK, (M3). 通过下例分别说明.
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)
在DB2中, M3如此实现:
Select * FROM
   (  Select MYKEY,DENSE_RANK() OVER (ORDER BY orD_COL DESC) AS MYRANK
      FROM MYTABLE ) AS RANKED_TABLE
Where MYRANK<=N

3. 为避免DBMSs的方言问题, M2,M3可以有如下标准(SQL 92)表示:
M2:
Select * FROM MYTABLE M1 Where N>
  &nbs

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店