1.在ORACLE中實現SELECT TOP N 由於ORACLE不支持SELECT TOP語句,所以在ORACLE中經常是用ORDER BY跟ROWNUM的組合來實現SELECT TOP N的查詢。
簡單地說,實現方法如下所示:
SELECT 列名1...列名n FROM
(SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
WHERE ROWNUM <= N(抽出記錄數)
ORDER BY ROWNUM ASC
下面舉個例子簡單說明一下。
顧客表customer(id,name)有如下數據:
ID NAME
01 first
02 Second
03 third
04 forth
05 fifth
06 sixth
07 seventh
08 eighth
09 ninth
10 tenth
11 last
則按NAME的字母順抽出前三個顧客的SQL語句如下所示:
SELECT * FROM
(SELECT * FROM CUSTOMER ORDER BY NAME)
WHERE ROWNUM <= 3
ORDER BY ROWNUM ASC
輸出結果為:
ID NAME
08 eighth
05 fifth
01 first