C#拼接SQL语句 用ROW_NUMBER实现的高效分页排序
发布时间:2023-12-27 04:47:01
如果项目中要用到数据库,铁定要用到分页排序。之前在做数据库查询优化的时候,通宵写了以下代码,来拼接分页排序的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”时要快。
最后,拜托哪位好心人士给测试下性能,拜托了,本人数据库菜鸟,不太懂得数据库的性能测试。
我只知道我对我写的分页排序还是很有信心的,(*^__^*) 嘻嘻!
首发:博客园->剑过不留痕
猜你喜欢
- 本文实例为大家分享了C# Winform实现波浪滚动效果的具体代码,供大家参考,具体内容如下设计思路1、首先,理解一个概念:正弦波,余弦波。
- 1.现象描述原来项目在Android studio 2.3一切正常,升级3.0之后报如下错误:Error:Cannot choose bet
- 目录I. 环境配置1. 项目配置2. 数据库表II. 传参类型确定1. 参数类型为整形2. 指定jdbcType3. 传参类型为String
- 这篇文章主要介绍了Java实现inputstream流的复制代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习
- android读取assets文件下的内容,一般都是使用getAsset.open()方法,并将文件的路径作为参数传入,而当我们解析一个目录
- java 线程池详解什么是线程池?提供一组线程资源用来复用线程资源的一个池子为什么要用线程池?线程的资源是有限的,当处理一组业务的时候,我们
- 本文实例讲述了C#定时关闭窗体的方法,分享给大家供大家参考。具体方法如下:public partial class Form2 : Form
- 前言有时,我们可能需要从 PDF 文档中提取表格数据,例如,当PDF发票的表格中存储了一些有用的信息,需要提取数据以进行进一步分析时。在这篇
- 文章导读本系列文章介绍从0开始搭建一个基于分布式的医疗挂号系统。本次四篇文章完成了医院设置微服务模块的后端接口,为了方便开发,对接口的返回结
- yaml中的数组类型取值yaml中简单的风格,十分受大家的欢迎先说下简单的配置如何取值# application-dev.yml testV
- 概述从今天开始, 小白我将带大家开启 Jave 数据结构 & 算法的新篇章.算法的衡量标准当我们需要衡量一个算法的的优越性, 通常会
- 一、效果展示初级难度中级难度高级难度测试界面二、项目介绍项目背景扫雷是一款大众类的益智小游戏。根据点击格子出现的数字找出所有非雷格子,同时避
- 一、多媒体应用架构1.1 音视频传统应用架构通常,传统的播放音频或视频的多媒体应用由两部分组成:播放器:用于吸收数字媒体并将其呈现为视频和/
- 这里写链接内容仿映客送小礼物的特效,顺便复习一下属性动画,话不多说先看效果图。需求分析可以看到整个动画有几部分组成,那我们就把每个部分拆分出
- 本文为大家分享了Android仿小度语音助手的贝塞尔曲线动画,供大家参考,具体内容如下废话不多说,看下面的动图,和百度的还是有点点差别,我也
- 本文实例为大家分享了java pdf加水印的具体代码,供大家参考,具体内容如下引入依赖<dependency> <grou
- 本文实例讲述了Java实现的上传并压缩图片功能。分享给大家供大家参考,具体如下:先看效果:原图:1.33M处理后:27.4kb关键代码:pa
- /** * 三角数字: * 比达哥斯拉领导下的古希腊数学家发现了一个有趣的数字序列1, 3, 6, 10, 15, 21,... *
- 本文将介绍如何通过Java程序来查找并高亮PDF中的文本。使用工具:Free Spire.PDF for Java(免费版)Jar文件获取及
- 前言首先,synchronized 是什么?我们需要明确的给个定义——同步锁,没错,它就是把锁。可以