currentPage:目前頁碼(從 1 開始)pageSize:每頁筆數| 資料庫 | 分頁語法 | 第 2 頁範例 (pageSize=10) |
|---|---|---|
| SQL Server (2012+) | SELECT * FROM Table ORDER BY Id OFFSET (@pageSize * (@currentPage-1)) ROWS FETCH NEXT @pageSize ROWS ONLY; |
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| SQL Server (2005-2008 R2) | SELECT TOP @pageSize * FROM Table
WHERE Id NOT IN (
SELECT TOP (@pageSize * (@currentPage-1)) Id FROM Table ORDER BY Id
)
ORDER BY Id; |
TOP 10 WHERE Id NOT IN (TOP 10 Id) |
| MySQL / MariaDB / PostgreSQL / SQLite | SELECT * FROM Table ORDER BY Id LIMIT @pageSize OFFSET @pageSize * (@currentPage-1); |
LIMIT 10 OFFSET 10 |
| Oracle (12c+) | SELECT * FROM Table ORDER BY Id OFFSET @pageSize * (@currentPage-1) ROWS FETCH NEXT @pageSize ROWS ONLY; |
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
| Oracle (11g 及舊版) | SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM Table ORDER BY Id
) a WHERE ROWNUM <= @pageSize * @currentPage
) WHERE rn > @pageSize * (@currentPage-1); |
WHERE rn > 10 AND rn <= 20 |
WHERE Id > @lastSeenId ORDER BY Id LIMIT @pageSize。ORDER BY 欄位必須有索引,否則全表排序極慢。public string BuildPageSql(string baseSql, DbType dbType, int page, int size) {
switch (dbType) {
case DbType.SqlServer2012: return $"{baseSql} ORDER BY Id OFFSET {size*(page-1)} ROWS FETCH NEXT {size} ROWS ONLY";
case DbType.SqlServerLegacy: return $"SELECT TOP {size} * FROM ({baseSql}) t WHERE Id NOT IN (SELECT TOP {size*(page-1)} Id FROM ({baseSql}) ORDER BY Id) ORDER BY Id";
case DbType.MySql: return $"{baseSql} ORDER BY Id LIMIT {size} OFFSET {size*(page-1)}";
case DbType.Oracle12c: return $"{baseSql} ORDER BY Id OFFSET {size*(page-1)} ROWS FETCH NEXT {size} ROWS ONLY";
case DbType.OracleLegacy:
return $"SELECT * FROM (SELECT a.*, ROWNUM rn FROM ({baseSql} ORDER BY Id) a WHERE ROWNUM <= {size*page}) WHERE rn > {size*(page-1)}";
default: throw new ArgumentException("Unsupported DB");
}
}