SQL实现分页查询方法总结

更新时间：2021年12月20日 10:32:28 作者：十九画生

这篇文章介绍了SQL实现分页查询方法总结，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

开发过程中经常遇到分页的需求，今天在此总结一下吧。

简单说来方法有两种，一种在源上控制，一种在端上控制。源上控制把分页逻辑放在SQL层；端上控制一次性获取所有数据，把分页逻辑放在UI上（如GridView）。显然，端上控制开发难度低，适于小规模数据，但数据量增大时性能和IO消耗无法接受；源上控制在性能和开发难度上较为平衡，适应大多数业务场景；除此之外，还可以根据客观情况（性能要求，源与端的资源占用等）在源和端之间加一层，应用特殊算法和技术进行处理。以下主要讨论源上，即SQL上的分页。

分页的问题其实就是在满足条件的一堆有序数据中截取当前所需要展示的那部分。实际上各种数据库都考虑到分页问题而内置了一些策略，比如MySql的LIMIT，Oracle的ROWNUM和ROW_NUMBER()，SqlServer的TOP和ROW_NUMBER()，基于此我们可以得到一系列分页的方法。

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

方法一、直接限制返回区间

SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;

优点：写法简单。

缺点：当页码和页大小过大时，性能明显下降。

适用：数据量不大。

2、基于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为主键时。

缺点：适用面窄，要求排序条件单一且可比较。

适用：简单排序（特殊情况也可尝试转换成类似可比较值处理）。

3、基于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。

适用：大部分分页查询需求。

到此这篇关于SQL实现分页查询方法总结的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

您可能感兴趣的文章:

SQL中日期格式处理方法大全
这篇文章主要给大家介绍了关于SQL中日期格式处理方法的相关资料,在SQL中日期格式化非常重要的,因日期SQL中的一个基数据类型,我们可以使用函数和选项来格式化日期,使用日期函数来处理日期数据,需要的朋友可以参考下
2023-09-09
Django项目优化数据库操作总结
这篇文章主要介绍了Django项目中优化数据库操作总结，有需要的朋友可以借鉴参考下，希望可以有所帮助，祝大家共进步，早日升职加薪
2021-09-09
数据库之Hive概论和架构和基本操作
Hive是一个构建在Hadoop上的数据仓库框架，最初，Hive是由Facebook开发，后台移交由Apache软件基金会开发，并做为一个Apache开源项目，感兴趣的同学可以参考阅读
2023-04-04
使用Navicat导入和导出sql语句的图文教程
Navicat是MySQL非常好用的可视化管理工具,功能非常强大,能满足我们日常数据库开发的所有需求,下面这篇文章主要给大家介绍了关于使用Navicat导入和导出sql语句的相关资料,需要的朋友可以参考下
2023-03-03
关于面试中常问的数据库回表问题
这篇文章主要介绍了关于面试中常问的数据库回表问题,回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树,需要的朋友可以参考下
2023-07-07
详解Navicat简单使用方法
这篇文章主要介绍了Navicat简单使用方法,本文通过图文并茂的形式给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
2020-11-11
详解关于Dbeaver的常用操作
这篇文章主要介绍了详解关于Dbeaver的常用操作，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
2020-11-11
Sybase 复制与热切换数据
SYBASE复制服务器(Sybase Replication Server)用来满足日益发展的企业客户/服务器计算机环境的需要。自从复制服务器被广泛应用以来，已成为企业范围内客户/服务器应用模式的基础。
2009-06-06
SQL 查询语句积累
SQL 查询语句积累...
2006-12-12
Navicat Premium 15 linux 安装与激活 ArchLinux 2022最新教程(完整激活
navicat premium mac是一款强大数据库管理软件，通过navicat premium 15 用户快速轻松地构建，管理和维护您的数据库，结合了其他Navicat软件使用更有意想不到的功能，这篇文章主要介绍了Navicat Premium 15 linux 安装与激活 ArchLinux 2022,需要的朋友可以参考下
2023-01-01