討論 SELECT TOP N 問題
關鍵字：Sql Server
Hi, everyone:
本貼討論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);