如果项目中要用到数据库，铁定要用到分页排序。
之前在做数据库查询优化的时候，通宵写了以下代码，来拼接分页排序的SQL语句。
1 /// <summary> 2 /// 单表（视图）获取分页SQL语句 3 /// </summary> 4 /// <param name="tableName">表名或视图名</param> 5 /// <param name="key">唯一键</param> 6 /// <param name="fields">获取的字段</param> 7 /// <param name="condition">查询条件（不包含WHERE）</param> 8 /// <param name="collatingSequence">排序规则（不包含ORDER BY）</param> 9 /// <param name="pageSize">页大小</param> 10 /// <param name="pageIndex">页码（从1开始）</param> 11 /// <returns>分页SQL语句</returns> 12 public static string GetPagingSQL( 13 string tableName, 14 string key, 15 string fields, 16 string condition, 17 string collatingSequence, 18 int pageSize, 19 int pageIndex) 20 { 21 string whereClause = string.Empty; 22 if (!string.IsNullOrEmpty(condition)) 23 { 24 whereClause = string.Format("WHERE {0}", condition); 25 } 26 27 if (string.IsNullOrEmpty(collatingSequence)) 28 { 29 collatingSequence = string.Format("{0} ASC", key); 30 } 31 32 StringBuilder sbSql = new StringBuilder(); 33 34 sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ',')); 35 sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex); 36 sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence); 37 sbSql.AppendFormat(" {0} ", key); 38 sbSql.AppendFormat(" FROM {0} ", tableName); 39 sbSql.AppendFormat(" {0} ", whereClause); 40 sbSql.AppendFormat(" ) AS [_TempTable_] "); 41 sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key); 42 sbSql.AppendFormat("WHERE [_TempTable_].[_RowNum_] > {0} ", pageSize * (pageIndex - 1)); 43 sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC "); 44 45 return sbSql.ToString(); 46 } 47 48 /// <summary> 49 /// 给字段添加表名前缀 50 /// </summary> 51 /// <param name="tableName">表名</param> 52 /// <param name="fields">字段</param> 53 /// <param name="separator">标识字段间的分隔符</param> 54 /// <returns></returns> 55 public static string PrependTableName(string tableName, string fields, char separator) 56 { 57 StringBuilder sbFields = new StringBuilder(); 58 59 string[] fieldArr = fields.Trim(separator).Split(separator); 60 foreach (string str in fieldArr) 61 { 62 sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator); 63 } 64 65 return sbFields.ToString().TrimEnd(separator); 66 }
假设有如下产品表：
1 CREATE TABLE [dbo].[Tbl_Product] 2 ( 3 [ID] [int] IDENTITY(1, 1) 4 NOT NULL , 5 [ProductId] [varchar](50) NOT NULL , 6 [ProductName] [nvarchar](50) NOT NULL , 7 [IsDeleted] [int] NOT NULL 8 CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) , 9 CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC ) 10 WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 11 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 12 ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] 13 ) 14 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语句如下（已手动调整了格式）：
1 SELECT Tbl_Product.ID , 2 Tbl_Product.ProductId , 3 Tbl_Product.ProductName 4 FROM ( SELECT TOP 25 5 [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) , 6 ID 7 FROM Tbl_Product 8 WHERE IsDeleted = 0 9 ) AS [_TempTable_] 10 INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID 11 WHERE [_TempTable_].[_RowNum_] > 20 12 ORDER BY [_TempTable_].[_RowNum_] ASC
- 查询的字段列表，去掉了不关心的字段（这里为IsDeleted，因为条件里面IsDeleted=0，查出来的产品都是没被删除的）；
- 排序依据，在调用该方法时，应尽量确保排序的依据可以唯一确定记录在结果集中的位置（这里添加了辅助排序依据，ID DESC，如果产品重名，添加的晚的排在前面）；
- 性能优化的一点儿建议：如果字段的值是统计出来的，通常是在视图中，且结果集很大，且需要对结果集分页排序，可以使用临时表规避不稳定的查询效率（关于不稳定的查询效率，会在之后的文章中示例）；
- 另外一点儿建议，使用ROW_NUMBER时，切记一定要和“TOP n”一起使用，n等于int.MaxValue都比不加“TOP n”时要快。
最后，拜托哪位好心人士给测试下性能，拜托了，本人数据库菜鸟，不太懂得数据库的性能测试。
我只知道我对我写的分页排序还是很有信心的，(*^__^*) 嘻嘻！
首发：博客园->剑过不留痕，欢迎转载，前提是注明出处：http://www.cnblogs.com/return8023/archive/2012/05/20/2510983.html
