博客

有时受制于源码框架或查询数据量过大的原因，不能在业务层做数据分页，只能在 SQL 上实现分页.

一、基于MySql的LIMIT和Oracle的ROWNUM，可以直接限制返回区间（以MySql为例，注意使用Oracle的ROWNUM时要应用子查询）：

方法一、直接限制返回区间
SELECT * FROM tablename WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
//优点：写法简单。
//缺点：当页码和页大小过大时，性能明显下降。
//适用：数据量不大。

二、基于LIMIT（MySql）、ROWNUM（Oracle）和TOP（SqlServer），他们可以限制返回的行数，因此可以得到以下两套通用的方法（以SqlServer为例）：

方法二、NOT IN
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN
(
    SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件
)
ORDER BY 排序条件
//优点：通用性强。
//缺点：当数据量较大时向后翻页，NOT IN中的数据过大会影响性能。
//适用：数据量不大。

方法三、MAX
SELECT TOP 页大小 * FROM table WHERE 查询条件 AND id >
(
    SELECT ISNULL(MAX(id),0) FROM 
    (
        SELECT TOP ((页码-1)*页大小) id FROM table WHERE 查询条件 ORDER BY id 
    ) AS tempTable
) 
ORDER BY id
//优点：速度快，特别是当id为主键时。
//缺点：适用面窄，要求排序条件单一且可比较。
//适用：简单排序（特殊情况也可尝试转换成类似可比较值处理）。

三、基于SqlServer和Oracle的ROW_NUMBER()，可以得到返回数据的行号，基于此在限制返回区间得到如下方法（以SqlServer为例）：

方法四、ROW_NUMBER()
SELECT TOP 页大小 * FROM 
(
    SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件
) AS tempTable
WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小
ORDER BY RowNum
//优点：在数据量较大时相比NOT IN有优势。
//缺点：小数据量时不如NOT IN。
//适用：大部分分页查询需求。

方法五
with A as (SELECT ROW_NUMBER()  over(order by 排序字段) as rownum,* from tablename WHERE 查询条件)
select * from A 
where rownum >((@pageindex - 1) * @pagesize) and rownum <=(@pageindex * @pagesize)  
order by rownum  

来源参考：

https://www.jb51.net/article/232381.htm