Oracle取TOP N條記錄

標籤：

在SQL Server裡面有top關鍵字可以很方便的取出前N條記錄，但是Oracle裡面卻沒有top的使用，類似實現取出前N條記錄的簡單方法如下：

方法1：利用ROW_NUMBER函數

取出前5條記錄：

SELECT NO FROM (    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST)WHERE RNO <= 5  ORDER BY NO ;

取出中間5條記錄：

SELECT NO FROM (    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST) WHERE RNO BETWEEN 5 AND 10 ORDER BY NO;

方法2：利用子查詢

取出前5條記錄：

SELECT NO FROM (    SELECT NO FROM ROWNUM_TEST  ORDER BY NO)WHERE ROWNUM <= 5  ORDER BY NO ;

取出中間5條記錄：

SELECT NO  FROM (    SELECT NO, ROWNUM RNO FROM (        SELECT NO FROM ROWNUM_TEST  ORDER BY NO    )) WHERE RNO BETWEEN 5 AND 10  ORDER BY NO;

 

Oracle取TOP N條記錄