限制 SQL 的查詢資料筆數

Jimi Hendrix - 謹以此圖紀念第一篇文章

常常在 SELECT 資料庫時,因為資料量龐大或是某些特殊原因,只想查詢特定筆數的資料,但因為各家資料庫的語法不盡相同,避免每次都要重新查詢一次,所以就整理起來。

Oracle

關鍵字 ROWNUM,放在WHERE 後方即可,範例語法如下:

上述語法的作用是查詢前 3 筆資料
1
SELECT * FROM TABLE_NAME WHERE ROWNUM <= 3;

需特別注意的地方有:

  1. ROWNUM會比 ORDER BY, GROUP BY 還要早執行,所以如果有使用這兩個子句的話,會發現顯示結果可能跟預期不同,建議用子查詢先包起來做 ORDER BY, GROUP BY 後再使用。
  2. ROWNUM就像一個序列,把資料一筆一筆放進去,當放進第一筆資料時,其值為 1,並且判斷是否符合條件式。若符合,則留下資料;不符合的話,就將該筆資料刪除,放進下一筆資料,ROWNUM從 1 開始。
用此語法會查無資料,因為永遠不會出現大於 3 的 ROWNUM
1
SELECT * FROM TABLE_NAME WHERE ROWNUM > 3;
查詢第 3 筆以後的資料
1
2
3
SELECT * FROM
(SELECT TABLE_NAME.*, ROWNUM AS NUM FROM TABLE_NAME)
WHERE NUM > 3;
取得第 2、3 筆資料
1
2
3
SELECT * FROM
(SELECT TABLE_NAME.*, ROWNUM AS NUM FROM TABLE_NAME)
WHERE NUM >=2 AND NUM <= 3;

MySQL

關鍵字LIMIT,可以接受一或兩個參數,範例如下:

查詢前 2 筆資料
1
2
SELECT * FROM TABLE_NAME LIMIT 2;
SELECT * FROM TABLE_NAME LIMIT 0, 2;
查詢第 2 到第 4 筆資料
1
SELECT * FROM TABLE_NAME LIMIT 1, 3;

SQL Server

在 SQL Server 中,查詢前 N 筆資料的語法為TOP,範例如下:

查詢前 3 筆資料
1
SELECT TOP 3 * FROM TABLE_NAME;

但若要在 SQL Server 中取出第 x 筆到第 y 筆資料,方法就有非常多種:

  • ROW_NUMBER 函式法:此函式會根據參數 OVER 內的 ORDER BY 順序給予資料列序號,再用這些序號當作條件去篩選,只適用於SQL Server 2005 以上版本

    查詢出第 2、3 筆資料
    1
    2
    3
    4
    SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as ROWNUM
    FROM TABLE_NAME
    ) a WHERE ROWNUM >= 2 and ROWNUM <= 3;
  • TOP相減法:將兩個 TOP 查詢結果相減來取得區間資料(TOP 3 - TOP 1 = 第 2、3 筆)。

    1
    2
    3
    SELECT TOP 3 * FROM TABLE_NAME
    MINUS
    SELECT TOP 1 * FROM TABLE_NAME;
  • TOP 包小 TOP 法:先找出 TOP 3 再倒序後找出其TOP 2

    1
    2
    3
    SELECT TOP 2 * FROM (
    SELECT TOP 3 * FROM TABLE_NAME ORDER BY ID
    ) a ORDER BY ID DESC;

使用這類功能時,切記要多注意 ORDER BYGROUP BY的使用,這會大大地影響回傳的資料內容。

Share Comments