在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
如果项目中要用到数据库,铁定要用到分页排序。 之前在做数据库查询优化的时候,通宵写了以下代码,来拼接分页排序的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]
调用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
最后,拜托哪位好心人士给测试下性能,拜托了,本人数据库菜鸟,不太懂得数据库的性能测试。 我只知道我对我写的分页排序还是很有信心的,(*^__^*) 嘻嘻! 首发:博客园->剑过不留痕,欢迎转载,前提是注明出处:http://www.cnblogs.com/return8023/archive/2012/05/20/2510983.html |
请发表评论