主流資料庫分頁查詢 SQL 寫法對照：SQL Server、Oracle、MySQL

SQL Server (利用 TOP 巢狀)

SELECT * FROM (
    SELECT TOP N * FROM (
        SELECT TOP (M + N - 1) * FROM table_name ORDER BY pk DESC
    ) t1
) t2 ORDER BY pk ASC;

範例：從第 10 筆起取 20 筆（pk=sys_id）

SELECT * FROM (
    SELECT TOP 20 * FROM (
        SELECT TOP 29 * FROM Sys_option ORDER BY sys_id DESC
    ) t1
) t2 ORDER BY sys_id ASC;

Oracle (利用 ROWNUM)

SELECT * FROM (
    SELECT ROWNUM r, t1.* FROM table_name t1 WHERE ROWNUM < M + N
) t2 WHERE t2.r >= M;

範例：

SELECT * FROM (
    SELECT ROWNUM r, t1.* FROM Sys_option t1 WHERE ROWNUM < 30
) t2 WHERE t2.r >= 10;

MySQL (最簡單：LIMIT)

SELECT * FROM table_name LIMIT M, N;

範例：

SELECT * FROM sys_option LIMIT 10, 20;

效能提示

• MySQL LIMIT 深分頁（大 offset）效能下降，建議改用「游標式分頁」：WHERE pk > last_pk ORDER BY pk LIMIT N。
• SQL Server 2012+ 可用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 標準語法。
• Oracle 12c+ 支援 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY。