討論: SELECT TOP N問題 (2)
關鍵字：Sql Server
Hi,everyone:
今天繼續討論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
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＞
( SELECT COUNT(*) FROM MYTABLE M2
WHERE M2.ORD_COL＞M1.ORD_COL);
注意: 兩個比較符都是 ＞
M3:
SELECT * FROM MYTABLE M1 WHERE N＞
( SELECT COUNT(DISTINCT M2.ORD_COL) FROM MYTABLE M2
WHERE M2.ORD_COL＞M1.ORD_COL);
注意: 與M2的不同在於DISTINCT的使用
至於M1, 我目前還沒有想到合適的標準寫法,請補充.
4. 關於MYSQL
相對於各主流RDBMS來說,MYSQL要簡單,幼稚地多. 在運行MYSQL時發現居然不支援SUBQUERY. 因此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的數值都一樣時. 如果哪位想到了如何解決這個問題,請補充.
----------------------------------------------------------------------------------
各語句已經測試過, 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);