SQL 面試經典題：查詢指定範圍行數據 (第 31-40 行) 多數據庫寫法

題目描述

表 table1，主鍵 ID 為自增但**可能不連續**。要求查詢第 31-40 行記錄 (按 ID 排序)。

標準解法：子查詢 + LIMIT/OFFSET (MySQL、PostgreSQL、SQLite)

SELECT * FROM table1 ORDER BY ID LIMIT 10 OFFSET 30;
-- 或
SELECT * FROM table1 ORDER BY ID LIMIT 30, 10;

原理： OFFSET 30 跳過前 30 行，LIMIT 10 取後 10 行。最簡潔、效能最好 (配合索引)。

SQL Server 寫法 (2012+ 支援 OFFSET/FETCH)

-- SQL Server 2012+
SELECT * FROM table1 ORDER BY ID OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY;

-- SQL Server 2008/2005 (經典三層嵌套)
SELECT TOP 10 * FROM (
    SELECT TOP 40 * FROM table1 ORDER BY ID
) AS t ORDER BY ID DESC;

舊版邏輯： 先取前 40 行，再倒序取前 10 行，再正序 (或應用層反轉)。

Oracle 寫法

-- Oracle 12c+ (標準 SQL)
SELECT * FROM table1 ORDER BY ID OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 11g 及之前 (ROWNUM 雙層嵌套)
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT * FROM table1 ORDER BY ID
    ) t WHERE ROWNUM <= 40
) WHERE rn > 30;

關鍵點： Oracle ROWNUM 在 WHERE 過濾**前**賦值，故需雙層嵌套：內層排序並限制 ROWNUM <= 40，外層過濾 rn > 30。

DB2 寫法

SELECT * FROM table1 ORDER BY ID FETCH FIRST 10 ROWS ONLY OFFSET 30 ROWS;

效能對比與最佳實踐

大偏移量 (Deep Pagination) 效能陷阱

當 OFFSET 1000000 LIMIT 10 時，數據庫需掃描並丟棄前 100 萬行，**極其低效**。

優化方案：游標分頁 / Keyset Pagination

-- 假設上一頁最後一條 ID = 95000
SELECT * FROM table1 WHERE ID > 95000 ORDER BY ID LIMIT 10;

• 利用索引直接定位，**無需掃描前 N 行**
• 適用於「下一頁」「載入更多」場景，不支援隨機跳頁
• 要求排序字段唯一 (如主鍵 ID)，否則需組合鍵 (sort_col, id)

總結

1. **優先使用標準 OFFSET ... FETCH** (現代版本) 或 LIMIT (MySQL/PG)
2. 舊版本 Oracle 用雙層 ROWNUM，SQL Server 用三層 TOP
3. **大數據量分頁嚴禁用大 OFFSET**，改用 Keyset Pagination
4. 面試時說出多種寫法並分析優缺點，加分項滿滿