C#拼接SQL语句，SQL Server 2005+，多行多列大数据量情况下，使用ROW_NUMBER实现的高效分页排序
如果项目中要用到数据库，铁定要用到分页排序。之前在做数据库查询优化的时候，通宵写了以下代码，来拼接分页排序的SQL语句。
1 ///
2 /// 单表（视图）获取分页SQL语句
3 ///
4 /// 表名或视图名
5 /// 唯一键
6 /// 获取的字段
7 /// 查询条件（不包含WHERE）
8 /// 排序规则（不包含ORDER BY）
9 /// 页大小
10 /// 页码（从1开始）
11 /// 分页SQL语句
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(" = 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 ");
41 sbSql.AppendFormat(" INNER JOIN {0} ON .{1} = {0}.{1} ", tableName, key);
42 sbSql.AppendFormat("WHERE . > {0} ", pageSize * (pageIndex - 1));
43 sbSql.AppendFormat("ORDER BY . ASC ");
44
45 return sbSql.ToString();
46 }
47
48 ///
49 /// 给字段添加表名前缀
50 ///
51 /// 表名
52 /// 字段
53 /// 标识字段间的分隔符
54 ///
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 .
2 (
3 IDENTITY(1, 1)
4 NOT NULL ,
5 (50) NOT NULL ,
6 (50) NOT NULL ,
7 NOT NULL
8 CONSTRAINT DEFAULT ( (0) ) ,
9 CONSTRAINT PRIMARY KEY CLUSTERED ( ASC )
10 WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
11 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
12 ALLOW_PAGE_LOCKS = ON ) ON
13 )
14 ON
[*]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 SELECTTbl_Product.ID ,
2 Tbl_Product.ProductId ,
3 Tbl_Product.ProductName
4 FROM ( SELECT TOP 25
5 = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) ,
6 ID
7 FROM Tbl_Product
8 WHERE IsDeleted = 0
9 ) AS
10 INNER JOIN Tbl_Product ON .ID = Tbl_Product.ID
11 WHERE . > 20
12 ORDER BY . ASC
[*]查询的字段列表，去掉了不关心的字段（这里为IsDeleted，因为条件里面IsDeleted=0，查出来的产品都是没被删除的）；
[*]排序依据，在调用该方法时，应尽量确保排序的依据可以唯一确定记录在结果集中的位置（这里添加了辅助排序依据，ID DESC，如果产品重名，添加的晚的排在前面）；
[*]性能优化的一点儿建议：如果字段的值是统计出来的，通常是在视图中，且结果集很大，且需要对结果集分页排序，可以使用临时表规避不稳定的查询效率（关于不稳定的查询效率，会在之后的文章中示例）；
[*]另外一点儿建议，使用ROW_NUMBER时，切记一定要和“TOP n”一起使用，n等于int.MaxValue都比不加“TOP n”时要快。
最后，拜托哪位好心人士给测试下性能，拜托了，本人数据库菜鸟，不太懂得数据库的性能测试。
我只知道我对我写的分页排序还是很有信心的，(*^__^*) 嘻嘻！
首发：博客园->剑过不留痕，欢迎转载，前提是注明出处：http://www.iyunv.com/return8023/archive/2012/05/20/2510983.html
页:
[1]