SQL Server 2005/2008/2012+ 三種經典分頁寫法,從語法到效能特徵完整對比,並附 Oracle 對照。
DECLARE @pagesize INT = 5, @currentpage INT = 2;
SELECT TOP @pagesize id, email, qq, wechat, phone, phone1
FROM contact
WHERE id NOT IN (
SELECT TOP (@pagesize * (@currentpage - 1)) id
FROM contact ORDER BY id ASC
)
ORDER BY id ASC;
NOT IN 含 NULL 會導致空結果、不支援複合 PK。SELECT TOP 5 *
FROM AISINO_BD_TelephoneRecord
WHERE ID > (
SELECT MAX(ID)
FROM (SELECT TOP (5 * (2 - 1)) ID FROM AISINO_BD_TelephoneRecord ORDER BY ID ASC) t
)
ORDER BY ID ASC;
SELECT *
FROM (
SELECT TOP (5 * (1 - 1) + 5)
ROW_NUMBER() OVER (ORDER BY Id ASC) AS rn, *
FROM AISINO_BD_TelephoneRecord
) t
WHERE rn > 5 * (1 - 1);
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY。SELECT *
FROM (
SELECT a.*, ROWNUM rn
FROM (SELECT * FROM aos_rms_user) a
WHERE ROWNUM <= 5 * (1 - 1) + 5
)
WHERE rn >= 5 * (1 - 1);
| 場景 | 推薦寫法 |
|---|---|
| 小表/低頻/簡單 PK | 寫法 1/2 皆可 |
| 大表/高頻/複雜排序 | 寫法 3 (ROW_NUMBER) 或 OFFSET/FETCH |
| 深分頁 (頁碼 > 1000) | 游標分頁 / Keyset Pagination (WHERE id > last_id ORDER BY id LIMIT N) |
現代專案優先使用 OFFSET/FETCH (2012+) 或 ROW_NUMBER(),棄用 NOT IN 與 MAX(ID) 寫法。