MSSQL Access 通用分頁存儲過程：TOP 多層巢狀動態 SQL 生成器 (C# 實現)

背景

SQL Server 2000/2005 早期版本無 ROW_NUMBER()/OFFSET-FETCH，需用 TOP 多層巢狀實現分頁。本文提供一個 C# 輔助類，自動生成最優分頁 SQL，支援雙向排序、首/尾頁優化、中間頁雙向策略。

核心類：PagingHelper

public static class PagingHelper
{
    /// 
生成分頁 SQL，排序欄位必須構成唯一鍵
    public static string CreatePagingSql(
        int totalCount, int pageSize, int pageIndex,
        string baseSql, string orderFields)
    {
        // 1. 解析並標準化排序欄位
        var orders = orderFields.Split(',', StringSplitOptions.RemoveEmptyEntries)
                                .Select(s => s.Trim()).ToArray();
        var origOrder = new StringBuilder();   // 原排序
        var revOrder  = new StringBuilder();   // 反向排序（分頁內層用）
        
        for (int i = 0; i < orders.Length; i++) {
            if (i > 0) { origOrder.Append(", "); revOrder.Append(", "); }
            origOrder.Append(orders[i]);
            
            int sp = orders[i].IndexOf(' ');
            if (sp > 0) {
                bool desc = orders[i].IndexOf(" desc", StringComparison.OrdinalIgnoreCase) >= 0;
                revOrder.Append(orders[i].Substring(0, sp)).Append(desc ? " ASC" : " DESC");
            } else {
                revOrder.Append(orders[i]).Append(" DESC");
            }
        }

        // 2. 計算總頁數、修正頁碼
        pageSize = pageSize == 0 ? totalCount : pageSize;
        int pageCount = (totalCount + pageSize - 1) / pageSize;
        pageIndex = Math.Max(1, Math.Min(pageIndex, pageCount));

        var sql = new StringBuilder();

        // 3. 首頁：單層 TOP
        if (pageIndex == 1) {
            sql.AppendFormat("SELECT TOP {0} * FROM ({1}) AS t ORDER BY {2}",
                             pageSize, baseSql, origOrder);
        }
        // 4. 尾頁：減少一層 TOP
        else if (pageIndex == pageCount) {
            int lastPageRows = totalCount - pageSize * (pageCount - 1);
            sql.Append("SELECT * FROM ( ")
               .AppendFormat("SELECT TOP {0} * FROM ({1}) AS t ORDER BY {2} ", 
                             lastPageRows, baseSql, revOrder)
               .Append(") AS t ORDER BY ").Append(origOrder);
        }
        // 5. 前半段：雙層 TOP (正序→反序→正序)
        else if (pageIndex < (pageCount / 2 + pageCount % 2)) {
            sql.Append("SELECT * FROM ( SELECT TOP ").Append(pageSize).Append(" * FROM ( ")
               .AppendFormat("SELECT TOP {0} * FROM ({1}) AS t ORDER BY {2} ",
                             pageSize * pageIndex, baseSql, origOrder)
               .Append(") AS t ORDER BY ").Append(revOrder)
               .Append(" ) AS t ORDER BY ").Append(origOrder);
        }
        // 6. 後半段：雙層 TOP (反序→正序)
        else {
            int revTop = (totalCount % pageSize) + pageSize * (pageCount - pageIndex);
            sql.AppendFormat("SELECT TOP {0} * FROM ( ", pageSize)
               .AppendFormat("SELECT TOP {0} * FROM ({1}) AS t ORDER BY {2} ",
                             revTop, baseSql, revOrder)
               .Append(") AS t ORDER BY ").Append(origOrder);
        }
        return sql.ToString();
    }

    public static string CreateCountingSql(string baseSql) 
        => $"SELECT COUNT(1) AS RecordCount FROM ({baseSql}) AS t";

    public static string CreateTopNSql(int n, string baseSql) 
        => $"SELECT TOP {n} * FROM ({baseSql}) AS t";
}

使用範例

string baseSql = "SELECT * FROM Orders WHERE CustomerID = 'ALFKI'";
string orderBy = "OrderDate DESC, OrderID DESC";
int total = (int)cmd.ExecuteScalar("SELECT COUNT(*) FROM (" + baseSql + ") t");
string pageSql = PagingHelper.CreatePagingSql(total, 20, 3, baseSql, orderBy);
// 執行 pageSql 獲取第 3 頁

關鍵設計點

• 排序欄位必須唯一：否則翻頁資料重複/遺漏
• 雙向策略：前半段從頭正序→反序取頁；後半段從尾反序→正序取頁，減少掃描行數
• 首尾頁優化：減少一層巢狀
• 動態 SQL 注入風險：baseSql 需經參數化或嚴格驗證

現代替代方案

• SQL Server 2012+：OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY
• SQL Server 2005-2008 R2：ROW_NUMBER() OVER (ORDER BY ...) + CTE
• 本方案僅供維護舊系統參考