SQL 方言參考/選擇查詢/限制返回的行數

限制返回的行數

注意：end_row = start_row + num_rows - 1

標準	SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY
DB2	SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY
Firebird	版本 > 2.0 SELECT columns FROM table ROWS start_row TO end_row 所有版本 SELECT FIRST num_rows SKIP start_row columns FROM table
Ingres	SELECT FIRST num_rows columns FROM table SELECT columns FROM table OFFSET start_row FETCH {FIRST|NEXT} num_rows ROWS ONLY
Linter	SELECT columns FROM table FETCH FIRST num_rows SELECT columns FROM table LIMIT start_row, num_rows (rows are numbered from 0)
MonetDB	SELECT columns FROM table LIMIT num_rows OFFSET start_row
MSSQL	SELECT columns FROM table OFFSET start_row ROWS FETCH { FIRST | NEXT } num_rows ROW[S] ONLY 簡單版本（當 start_row = 0 時） SELECT TOP num_rows columns FROM table
MySQL	版本 > 4.0.14 SELECT columns FROM table LIMIT num_rows OFFSET start_row 所有版本 SELECT columns FROM table LIMIT start_row, num_rows
Oracle	簡單查詢 -- Notice: Will not work, if start_row > 1, since the first row will return false, and the cursor will terminate. SELECT columns FROM table WHERE rownum >= start_row AND rownum <= end_row 這在不指定 start_row 的情況下也能正常工作 SELECT columns FROM table WHERE rownum <= end_row 這也能工作，但結果集中會附加 num 列 SELECT * FROM ( SELECT temp.*, rownum num FROM table ORDER BY columns ) WHERE num >= start_row and num <= end_row 自 12.1 起，類似於標準 SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY 完整語法：（可以有 OFFSET、FETCH 或兩個子句） SELECT column [, column2 …] FROM table [ORDER BY column2] [OFFSET start_row ROWS] [FETCH [FIRST|NEXT] [num_rows|percent PERCENT] ROWS [ONLY|WITH TIES]]
PostgreSQL	SELECT columns FROM table LIMIT num_rows OFFSET start_row 或：SQL 標準語法。
SQLite	SELECT columns FROM table LIMIT num_rows OFFSET start_row
Virtuoso	SELECT TOP num_rows columns FROM table SELECT TOP skip_rows,num_rows columns FROM table

標準	-
DB2	?
Firebird	?
Ingres	?
Linter	?
MonetDB	?
MSSQL	NOT IN()
MySQL	NOT IN()
Oracle	MINUS
PostgreSQL	?
SQLite	?
Virtuoso	?