构造获取分页操作SQL语句

最新推荐文章于 2020-03-08 13:20:12 发布
最新推荐文章于 2020-03-08 13:20:12 发布
阅读量60 收藏
点赞数
构造获取分页操作SQL语句

/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引，优化分页提取方式)
/// </summary>
/// <param name="tblName">操作表名称</param>
/// <param name="fldName">排序的索引字段</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="totalRecord">总记录数</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
int totalRecord,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";
string rtnFields = "*";

// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");

strOldWhere = " AND " + strWhere + " ";

strWhere = " WHERE " + strWhere + " ";
}

// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}

return strSQL;
}


/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";

// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");

strOldWhere = " AND " + strWhere + " ";

strWhere = " WHERE " + strWhere + " ";
}

// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}

return strSQL;
}


/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="unionCondition">用于连接的条件，例如: LEFT JOIN UserInfo u ON (u.UserID = b.UserID)</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式，0升序，1为降序</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL(string tblName,
string fldName,
string unionCondition,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";

// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");

strOldWhere = " AND " + strWhere + " ";

strWhere = " WHERE " + strWhere + " ";
}

// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";

//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + "ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";

strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";

//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + "ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + unionCondition + " ";

strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}

return strSQL;
}





/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_TOP(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";

// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");

strOldWhere = " AND " + strWhere + " ";

strWhere = " WHERE " + strWhere + " ";
}

// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += strWhere + " ORDER BY " + fldName + " ASC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += strWhere + " ORDER BY " + fldName + " DESC";
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
}
}
else // 异常处理
{
throw new DataException("未指定任何排序类型。0升序，1为降序");
}

return strSQL;
}






/// <summary>
/// 获取分页操作SQL语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblName">操作表名</param>
/// <param name="fldName">操作索引字段名称</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="rtnFields">返回字段集合，中间用逗号格开。返回全部用“*”</param>
/// <param name="OrderType">排序方式(0升序，1为降序)</param>
/// <param name="sort">排序表达式</param>
/// <param name="strWhere">检索的条件语句，不需要再加WHERE关键字</param>
/// <returns></returns>
public static string ConstructSplitSQL_sort(string tblName,
string fldName,
int PageIndex,
int PageSize,
string rtnFields,
int OrderType,
string sort,
string strWhere)
{
string strSQL = "";
string strOldWhere = "";

// 构造检索条件语句字符串
if (strWhere != "")
{
// 去除不合法的字符，防止SQL注入式攻击
strWhere = strWhere.Replace("'", "''");
strWhere = strWhere.Replace("--", "");
strWhere = strWhere.Replace(";", "");

strOldWhere = " AND " + strWhere + " ";

strWhere = " WHERE " + strWhere + " ";
}

if (sort != "") sort = " ORDER BY " + sort;

// 升序操作
if (OrderType == 0)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " >= ( SELECT MAX(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " ASC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " ASC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " > ( SELECT MAX(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";

strSQL += strOldWhere + sort;
}
}
// 降序操作
else if (OrderType == 1)
{
if (PageIndex == 1)
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

//strSQL += "WHERE (" + fldName + " <= ( SELECT MIN(" + fldName + ") FROM (SELECT TOP 1 " + fldName + " FROM " + tblName + strWhere + " ORDER BY " + fldName + " DESC ) AS T )) ";

//strSQL += strOldWhere + "ORDER BY " + fldName + " DESC";
strSQL += strWhere + sort;
}
else
{
strSQL += "SELECT TOP " + PageSize + " " + rtnFields + " FROM " + tblName + " ";

strSQL += "WHERE (" + fldName + " < ( SELECT MIN(" + fldName + ") FROM (SELECT TOP " + ((PageIndex - 1) * PageSize) + " " + fldName + " FROM " + tblName + strWhere + sort + " ) AS T )) ";

strSQL += strOldWhere + sort;
}
}
else // 异常处理
{
throw new DataException("未指定主索引排序类型。0升序，1为降序");
}

return strSQL;
}
iteye_4537
关注
java分页sql语句_「sql分页」sql语句 实现分页 - seo实验室
weixin_42191480的博客
02-25 1050
sql分页sql语句 实现分页/*分页思想：比如你要每页获取10条记录，当你显示第5页的记录时，也就是选取第40条至50条的记录.首先应该从所有的记录集中选取50条记录，同时进行倒序,再从中选10条，就完成工作了。下面是一个具体的例子，从Northwind的orders表中选取OrderID大于@OrderID的记录集，分10条每页显示.*/--有查询条件的分页存储过程createprocedu...
Laravel框架执行原生SQL语句及使用paginate分页的方法
12-20
在Laravel框架中，开发人员有时需要执行原生SQL语句或者使用内置的查询构建器。本篇文章将探讨如何在Laravel中实现这两种方法，并结合`paginate`分页功能。 首先，我们来看如何执行原生SQL语句。在Laravel中，你...
简单分页的Sql文件
06-18
简单分页的Sql文件,可直接使用，生成数据库表结构和数据
SQL获取分页数据
weixin_30243533的博客
09-22 160
public DataSet GetPagingData(string table, string fields, string filter, string sort, int pageSize, int pageIndex) { IGSPDatabase db = CommonCore.ComDatabase.GetDatabase; ...
分页功能中使用的SQL语句 MySQL与SQL server
drive_的博客
05-30 266
javaWeb中经常会需要实现分页 数据库语句需要查询第n条记录到第m条记录之间的内容 这里SQL  server 跟mySQL 的查询语句有所不同，具体为： SQL server：         select  top n * from table;    //这是查询前n条   *代表查询所有内容，如果需要查询某一列，则将*换成列名即可   若需要查询第5到第10条的话：（
Laravel 使用查询构造器配合原生sql语句查询的例子
10-16
总的来说，Laravel的查询构造器配合原生SQL语句，可以有效地平衡简洁性和灵活性，让开发者在处理数据库操作时更加得心应手。在实际开发中，根据具体需求选择合适的方法，可以提高代码的可读性和可维护性。同时，了解...
SQL分页语句的三种方案
09-10
此方法的效率通常优于方案一，但仍然需要动态构造SQL语句。 ### 方案二倒序 对于需要按降序排列的数据，我们可以稍微修改上面的方案二，如下所示： ```sql SELECT TOP 页大小 * FROM table1 WHERE ID ( SELECT ...
php mssql 分页SQL语句优化 持续影响
10-29
通过计算出合适的`$top1` 和 `$top2` 值，以及根据排序方式进行相应的调整，构造出了最终的SQL语句。 #### 六、总结 通过以上的分析和代码示例，我们可以看出，在PHP和MSSQL环境下，通过合理的SQL语句设计和优化，...
sql语句 实现分页
热门推荐
Junit的博客
05-19 4万+
      sql语句 实现分页 /* 分页思想：比如你要每页获取10条记录，当你显示第5页的记录时， 也就是选取第40条至50条的记录.首先应该从所有的记录集中选取 50条记录，同时进行倒序,再从中选10条，就完成工作了。 下面是一个具体的例子，从Northwind的Orders表中选取OrderID大于@OrderID的记录集， 分10条每页显示. */ --有查询条件的分页存储...
SQL中分页查询
weixin_36451489的博客
09-06 993
SQL查询语句中的 limit 与 offset 的区别： limit y 分句表示: 读取 y 条数据 limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据 limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据 练习题：leetcode[176]第二高的薪水 查询语句： select(select distinct Salary from...
SQL分页查询
徐小冠
03-08 3万+
SQL分页查询: 背景 ​ 在公司的系统中有一个平台是 做配置管理的 就是所谓的 CRUD 的平台,但是点击后进去到页面第一次看到的是一次查询的页面 (点击页面自动的触发查询的功能) 后面就可以你的CRUD的操作是给运营的同事来操作的,但是一般的是我们数据量比较的少的业务是之间查询出来所有的数据,直接返回给前端的让他自己做分页的,但是有一些数据量达到上万级别的时候,不能让他们乱搞了,...
SQL分页获取数据
weixin_30820077的博客
05-05 254
SQL Server分页 select * from (select row_number() over(order by MaterialNM) as rowId, * from MDMMaterials where EditState='0') t where rowId between 1 and 100 Oracle分页 SELECT * FROM (SELEC...
SQL性能优化第一篇之分页数据与Count数据一次性获取
小达哥的博客
11-09 1万+
相信大部分人都会遇到：在数据库的数据量很大时，分页需要几秒钟才会全部完成；包括分页list的获取和count的获取。那我们完全可以将这两步放到一次sql去执行获取，减少一半的查询时间。这里get到sql_calc_found_rows和SELECT FOUND_ROWS()这两个知识点。看代码： select SQL_CALC_FOUND_ROWS col_name1 as colN...
文件转换为Base64形式（可来回转换，带加密功能）
最新发布
10-09
文件转换为Base64形式（可来回转换，带加密功能） 使用说明 === https://blog.csdn.net/sxzlc/article/details/139076076 ====
基于粒子群算法的配电网日前优化调度 采用IEEE33节点配电网搭建含风光，储能，柴油发电机和燃气轮机的经济调度模型 以运行成本
10-09
基于粒子群算法的配电网日前优化调度 采用IEEE33节点配电网搭建含风光，储能，柴油发电机和燃气轮机的经济调度模型。 以运行成本和环境成本最小为目标，考虑储能以及潮流等约束，采用粒子群算法对模型进行求解，得到电源的每小时出力情况。
ECU ccp协议上位机测试与标定源码，C#源码上位机ECU标定与测量程序 - ASAM MCD -1MC的 ccp 标定与测
10-09
ECU ccp协议上位机测试与标定源码，C#源码上位机ECU标定与测量程序。 - ASAM MCD -1MC的 ccp 标定与测量上位机软件，短上传和DAQ功能，支持一维二维等测量和标定，显示控件丰富，具有仪表盘，进度条，二维map等。 - ASAM MCD-2MC 的文件读取（a2l文件读取） - 在解析的同时提供了文件结构检查 - 读取和导出ECU 测量数据为excel文件 - 支持多个设备 ，包括 ETAs, Vector, Peak, 8Devices, Kvaser, usbcan兼容设备 等等CAN设备的支持，同时有一个简单的接口，用于实现其他设备支持，接口dll函数替即可。 .Net 目标框架为 4.0，需要引用Excel程序，默认版本为 office 2019，也可在源码中修改。
SQLServer2005存储过程实现分页查询
为了获取总记录数，存储过程构造了一个动态SQL语句，利用`sp_executesql`执行这个语句并获取结果。然后，根据当前页码和每页大小计算出分页查询的起始和结束记录号。最后，构建用于分页查询的SQL语句，同样使用动态...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值