關于 SELECT N 問題
2024-07-21 02:38:16
供稿：網友
這里描述的 SELECT N 包括這樣幾種情況:
1. 選取TOP N行記錄
2. 選取N1-N2行記錄
3. 選取FOOT N行記錄
當然需要考慮是否有ORDER BY子句的情況, 下面試以系統視圖CAT為例分別說明.
注: A. 為沒有ORDER BY的情況
B. 有ORDER BY的情況
1. 選取 TOP N 行記錄
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N
2. 選取N1-N2行記錄
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUM B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1+1 AND N2;
3. 選取FOOT N行記錄
這里是說明不知道記錄集的記錄個數的情況, 假如已知, 用上面2的方法即可
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
或
SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)
WHERE ROWNUM<=N
以上在Oracle8.1.5 for Windows2000PRo 上測試通過
