前言
一直没什么机会自己从头写一个分页方法,最近工作的一个项目里面的分页方法不太适用目前使用的情况,然后自己从头写了一个
代码实现
注释:因为加了一个子查询,所以入参的sql只要是直接能在数据库查询出来的,都可以直接通过sql传入;入参的order的例子(trueName desc)
/// <summary>
/// 便捷子查询分页sql
/// </summary>
/// <param name="sql">主sql语句</param>
/// <param name="order">排序字段</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">页数</param>
/// <returns></returns>
public static string FaPageString(string sql, string order, int pageSize, int pageIndex)
{
string searchSql = $" ROW_NUMBER() OVER (ORDER BY {order}) AS RowNum ";
int startPage = (pageIndex - 1) * pageSize + 1;
int endPage = pageIndex * pageSize;
var result =
$@"WITH OrderedTables AS (
select
*,{searchSql}
from (
{sql}
) as T
)
SELECT
*
FROM
OrderedTables
WHERE
RowNum BETWEEN {startPage} AND {endPage}
ORDER BY
RowNum;
";
return result;
}
基础sql语句
------基础结构
WITH OrderedTables AS (
select
*, ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNum
from (
SELECT *
FROM Table
) as T
)
SELECT
*
FROM
OrderedTables
WHERE
RowNum BETWEEN 1 AND 20
ORDER BY
RowNum;