在不使用第三方分頁組件的前提下,利用 SQL NOT IN 子查詢配合前端 JS 渲染,可實現高效、靈活的分頁功能。本文提供完整後端邏輯與前端交互代碼。
-- 首頁:直接取前 N 條
SELECT TOP 15 * FROM dingdinfo ORDER BY dingdh DESC
-- 第 page 頁:排除前 (page-1)*15 條記錄的 ID
SELECT TOP 15 * FROM dingdinfo
WHERE id NOT IN (
SELECT TOP ((page-1)*15) id FROM dingdinfo ORDER BY dingdh DESC
)
ORDER BY dingdh DESC
優勢:無需游標分頁,避免 AbsolutePage 性能開銷;適合中小數據量(萬級以內)場景。
public ActionResult Sys(string page)
{
int pageSize = 15;
string baseSql = "SELECT * FROM dingdinfo ORDER BY dingdh DESC";
if (String.IsNullOrEmpty(page))
{
ViewData["ds"] = dr.ResultSet($"SELECT TOP {pageSize} * FROM dingdinfo ORDER BY dingdh DESC");
}
else
{
int pageNum = Convert.ToInt32(page);
int offset = (pageNum - 1) * pageSize;
string sql = $@"SELECT TOP {pageSize} * FROM dingdinfo
WHERE id NOT IN (SELECT TOP {offset} id FROM dingdinfo ORDER BY dingdh DESC)
ORDER BY dingdh DESC";
ViewData["ds"] = dr.ResultSet(sql);
}
// 總頁數計算
int total = dr.ResultCount("SELECT COUNT(*) FROM dingdinfo");
int pageCount = (total + pageSize - 1) / pageSize; // 向上取整
ViewData["jg"] = pageCount;
return View();
}
<script>
function GoPage(pageNum) {
window.location.href = "/bookIndex/sys/?page=" + pageNum;
}
</script>
<td>
<%
int totalPages = Convert.ToInt32(ViewData["jg"] ?? 0);
for (int i = 1; i <= totalPages; i++) {
%>
<a href="#" onclick="GoPage(<%: i %>)" class="<%: i == (ViewData["page"]?.ToString() ?? "1") ? 'active' : '' %>"><%: i %></a>
<% } %>
</td>
ROW_NUMBER() OVER (ORDER BY ...) 窗口函數分頁(SQL Server 2005+)。dingdh 和主鍵 id 有索引。COUNT(*) 掃描。NOT IN 子查詢中 id 不可為 NULL,否則結果為空。dingdh 作為輔助排序。此方案代碼量小、無組件依賴、邏輯清晰,是中小型 ASP/ASP.NET MVC 專案的理想分頁起點。