-
C#教程之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”时要快。
复制代码 代码如下:
/// <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”时要快。
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
SQL Server -- 解决存储过程传入参数作为s
JavaScript判断两个数组相等的四类方法
js如何操作video标签
React实战--利用甘特图和看板，强化Paas平
【记录】正则替换的偏方
前端下载 Blob 类型整理
抽象语法树AST必知必会
关于JS定时器的整理
JS中使用Promise.all控制所有的异步请求都完
js中字符串的方法
import-local执行流程与node模块路径解析流程
