C#拼接SQL语句 用ROW_NUMBER实现的高效分页排序
程序员文章站
2023-12-01 21:30:22
如果项目中要用到数据库，铁定要用到分页排序。之前在做数据库查询优化的时候，通宵写了以下代码，来拼接分页排序的sql语句 复制代码 代码如下: ///
如果项目中要用到数据库，铁定要用到分页排序。之前在做数据库查询优化的时候，通宵写了以下代码，来拼接分页排序的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”时要快。
最后，拜托哪位好心人士给测试下性能，拜托了，本人数据库菜鸟，不太懂得数据库的性能测试。
我只知道我对我写的分页排序还是很有信心的，(*^__^*) 嘻嘻！
首发：博客园->剑过不留痕