C#拼接SQL語句 用ROW_NUMBER實現的高效分頁排序

如果項目中要用到資料庫，鐵定要用到分頁排序。之前在做資料庫查詢最佳化的時候，通宵寫了以下代碼，來拼接分頁排序的SQL語句

複製代碼 代碼如下:/// <summary>
/// 單表（視圖）擷取分頁SQL語句
/// </summary>
/// <param name="tableName">表名或視圖名</param>
/// <param name="key">唯一鍵</param>
/// <param name="fields">擷取的欄位</param>
/// <param name="condition">查詢條件（不包含WHERE）</param>
/// <param name="collatingSequence">定序（不包含ORDER BY）</param>
/// <param name="pageSize">頁大小</param>
/// <param name="pageIndex">頁碼（從1開始）</param>
/// <returns>分頁SQL語句</returns>
public static string GetPagingSQL(
string tableName,
string key,
string fields,
string condition,
string collatingSequence,
int pageSize,
int pageIndex)
{
string whereClause = string.Empty;
if (!string.IsNullOrEmpty(condition))
{
whereClause = string.Format("WHERE {0}", condition);
}

if (string.IsNullOrEmpty(collatingSequence))
{
collatingSequence = string.Format("{0} ASC", key);
}

StringBuilder sbSql = new StringBuilder();

sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ','));
sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex);
sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence);
sbSql.AppendFormat(" {0} ", key);
sbSql.AppendFormat(" FROM {0} ", tableName);
sbSql.AppendFormat(" {0} ", whereClause);
sbSql.AppendFormat(" ) AS [_TempTable_] ");
sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key);
sbSql.AppendFormat("WHERE [_RowNum_] > {0} ", pageSize * (pageIndex - 1));
sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC ");

return sbSql.ToString();
}

/// <summary>
/// 給欄位添加表名首碼
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">欄位</param>
/// <param name="separator">識別欄位間的分隔字元</param>
/// <returns></returns>
public static string PrependTableName(string tableName, string fields, char separator)
{
StringBuilder sbFields = new StringBuilder();

string[] fieldArr = fields.Trim(separator).Split(separator);
foreach (string str in fieldArr)
{
sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator);
}

return sbFields.ToString().TrimEnd(separator);
}

假設有如下產品表： 複製代碼 代碼如下:CREATE TABLE [dbo].[Tbl_Product]
(
[ID] [int] IDENTITY(1, 1)
NOT NULL ,
[ProductId] [varchar](50) NOT NULL ,
[ProductName] [nvarchar](50) NOT NULL ,
[IsDeleted] [int] NOT NULL
CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) ,
CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]

Tbl_Product->ID（序號，非空，自增）
Tbl_Product->ProductId（產品Id，主鍵）
Tbl_Product->ProductName（產品名稱，非空）
Tbl_Product->IsDeleted（虛擬刪除標記，非空）
調用BasicFunction.GetPagingSQL("Tbl_Product", "ID", "ID,ProductId,ProductName", "IsDeleted=0", "ProductName ASC, ID DESC", 5, 5)，BasicFunction為分頁排序方法所在的靜態類，產生的分頁排序SQL語句如下（已手動調整了格式）： 複製代碼 代碼如下:SELECT Tbl_Product.ID ,
Tbl_Product.ProductId ,
Tbl_Product.ProductName
FROM ( SELECT TOP 25
[_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) ,
ID
FROM Tbl_Product
WHERE IsDeleted = 0
) AS [_TempTable_]
INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID
WHERE [_RowNum_] > 20
ORDER BY [_TempTable_].[_RowNum_] ASC

查詢的欄位列表，去掉了不關心的欄位（這裡為IsDeleted，因為條件裡面IsDeleted=0，查出來的產品都是沒被刪除的）；
排序依據，在調用該方法時，應盡量確保排序的依據可以唯一確定記錄在結果集中的位置（這裡添加了輔助排序依據，ID DESC，如果產品重名，添加的晚的排在前面）；
效能最佳化的一點兒建議：如果欄位的值是計算出來的，如：總價=單價*數量，而此時需要總價大於多少的記錄，還得拿總價遞增或者遞減排序，如果不要暫存資料表，資料量大的時候，就等著買新電腦吧！你問我為什麼要買新電腦，哦，因為你會把現在的電腦砸掉！O(∩_∩)O~
另外一點兒建議，使用ROW_NUMBER時，切記一定要和“TOP n”一起使用，n等於int.MaxValue都比不加“TOP n”時要快。
最後，拜託哪位好心人士給測試下效能，拜託了，本人資料庫菜鳥，不太懂得資料庫的效能測試。

我只知道我對我寫的分頁排序還是很有信心的，(*^__^*) 嘻嘻！

首發：部落格園->劍過不留痕