網頁

2017/9/12

Oracle 如何查詢前N筆資料

在Oracle中要限制查詢回傳的資料筆數時,可用ROWNUM


例如資料表中有1000筆資料,只查出前10筆的SQL如下

SELECT * FROM table_name 
WHERE ROWNUM < 10;

要注意的是ROWNUM是Pseudocolumn(偽欄位),執行順序先於ORDER BYGROUP BY,所以ORDER BY時,要搭配子查詢(sub query)使用ROWNUM的順序才正確。

SELECT ROWNUM, t.* FROM (
    SELECT * FROM table_name ORDER BY column_name
) t WHERE ROWNUM < 10; 

例如MY_STUDENTS資料表內容如下:

  STUDENT_ID   STUDENT_NAME   SCORE  
 ------------ -------------- ------- 
  0000000001   John              80  
  0000000002   Bill              56  
  0000000003   Mary              77  
  0000000004   Ann               69  
  0000000005   Emmy              92  
  0000000006   Lee               89  
  0000000007   Duke              43  
  0000000008   Smith             52  
  0000000009   Tom               75  

ORDER BY以SCORE排序時,ROWNUM的順序會亂掉。

SELECT ROWNUM, t.* FROM MY_STUDENTS t ORDER BY SCORE;
  ROWNUM   STUDENT_ID   STUDENT_NAME   SCORE  
 -------- ------------ -------------- ------- 
       7   0000000007   Duke              43  
       8   0000000008   Smith             52  
       2   0000000002   Bill              56  
       4   0000000004   Ann               69  
       9   0000000009   Tom               75  
       3   0000000003   Mary              77  
       1   0000000001   John              80  
       6   0000000006   Lee               89  
       5   0000000005   Emmy              92  

要利用子查詢ROWNUM才會由小到大遞增。

SELECT ROWNUM, t.* FROM (
    SELECT * FROM MY_STUDENTS ORDER BY SCORE
) t;
  ROWNUM   STUDENT_ID   STUDENT_NAME   SCORE  
 -------- ------------ -------------- ------- 
       1   0000000007   Duke              43  
       2   0000000008   Smith             52  
       3   0000000002   Bill              56  
       4   0000000004   Ann               69  
       5   0000000009   Tom               75  
       6   0000000003   Mary              77  
       7   0000000001   John              80  
       8   0000000006   Lee               89  
       9   0000000005   Emmy              92  

查出分數最低的學生。

SELECT ROWNUM, t.* FROM (
    SELECT * FROM MY_STUDENTS ORDER BY SCORE
) t WHERE ROWNUM = 1;
  ROWNUM   STUDENT_ID   STUDENT_NAME   SCORE  
 -------- ------------ -------------- ------- 
       1   0000000007   Duke              43  

查出分數最高的前三名的學生。

SELECT ROWNUM, t.* FROM (
    SELECT * FROM MY_STUDENTS ORDER BY SCORE DESC
) t WHERE ROWNUM <= 3;
  ROWNUM   STUDENT_ID   STUDENT_NAME   SCORE  
 -------- ------------ -------------- ------- 
       1   0000000005   Emmy              92  
       2   0000000006   Lee               89  
       3   0000000001   John              80  

這種查詢前幾筆資料的語法在MSSQL是使用TOP,而在MySQL為LIMIT

-- MS SQL
SELECT TOP 10 * FROM table_name ORDER BY column_name;

-- MySQL
SELECT * FROM table_name ORDER BY column_name LIMIT 10;

本篇如有幫助到您還幫忙按個廣告支持。


沒有留言:

張貼留言