sql语句实现分页(SQL 窗口函数实现高效分页查询的案例分析)
sql语句实现分页
SQL 窗口函数实现高效分页查询的案例分析
🍺不闻不若闻之,闻之不若见之,见之不若知之,知之不若行之。学至于行之而止矣。——荀子
大家好!我是只谈技术不剪发的 Tony 老师。
在使用 SQL 语句实现分页查询时,我们需要知道一些额外的参数信息,例如查询返回的总行数、当前所在的页数、最后一页的页数等。在传统的实现方法中我们需要执行额外的查询语句获得这些信息,本文介绍一种只需要一个查询语句就可以返回所有数据的方法,也就是通过 SQL 窗口函数实现高效的分页查询功能。
本文使用的示例表和数据可以这里下载。
传统方法实现分页查询
在 SQL 中实现分页查询的传统方法就是利用标准的 OFFSET … FETCH 语句或者许多数据库支持的 LIMIT … OFFSET 语句,例如:
-- Oracle、SQL Server、PostgreSQL SELECT emp_name, sex, email FROM employee ORDER BY emp_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- MySQL、PostgreSQL、SQLite SELECT emp_name, sex, email FROM employee ORDER BY emp_id LIMIT 10 OFFSET 10;
以上语句非常容易理解,返回的是第 2 页中的 10 条记录。但是问题在于我们如何知道总共包含多少页数据(或者总的记录数),显然在此之前我们需要执行另一个查询:
SELECT COUNT(*) FROM employee; COUNT(*)| --------+ 25|
有了总的记录数 25 之后,我们可以计算出数据总共有 3 页,每页 10 条。
这种方法要求我们每次进行分页查询时都需要执行 2 个查询语句,使用起来不是很方便。下面我们介绍更加高效的窗口函数分页查询。
📝关于分页查询的实现,OFFSET 分页对于大量数据的分页可能存在性能问题,另一种方法就是采用键集分页(keyset pagination)。
窗口函数实现分页查询
首先让我们考虑一下使用 OFFSET 分页查询时需要哪些参数:
- TOTAL_ROWS,总记录数;
- CURRENT_PAGE,当前所在页码;
- MAX_PAGE_SIZE,每一页最多显示的记录数,例如 10、20、50;
- ACTUAL_PAGE_SIZE,当前页实际包含的记录数;
- ROW_NBR,每条记录的实际偏移量;
- LAST_PAGE,当前页是否是最后一页。
每一页最多显示的记录数(MAX_PAGE_SIZE)是我们传递给数据库的参数,其他则是查询返回的结果,我们可以通过下面的查询语句实现所有的功能:
-- Oracle、SQL Server、PostgreSQL WITH e AS ( -- 初始查询 SELECT emp_id, emp_name, sex, email FROM employee ), t AS ( SELECT emp_id, emp_name, sex, email, COUNT(*) OVER () AS total_rows, -- 总记录数 ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量,ORDER BY和初始查询相同 FROM e ORDER BY e.emp_id -- 排序 OFFSET 10 ROWS -- 分页 FETCH NEXT 10 ROWS ONLY ) SELECT emp_id, emp_name, sex, email, COUNT(*) OVER () AS actual_page_size, -- 当前页实际记录数 CASE MAX(row_nbr) OVER () WHEN total_rows THEN 'Y' ELSE 'N' END AS last_page, -- 是否最后一页 total_rows, -- 总记录数 row_nbr, -- 每一条数据的偏移量 ((row_nbr - 1) / 10) + 1 AS current_page -- 当前所在页码 FROM t ORDER BY emp_id; -- MySQL、PostgreSQL、SQLite WITH e AS ( -- 初始查询 SELECT emp_id, emp_name, sex, email FROM employee ), t AS ( SELECT emp_id, emp_name, sex, email, COUNT(*) OVER () AS total_rows, -- 总记录数 ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量,ORDER BY和初始查询相同 FROM e ORDER BY e.emp_id -- 排序 LIMIT 10 OFFSET 10 ROWS -- 分页 ) SELECT emp_id, emp_name, sex, email, COUNT(*) OVER () AS actual_page_size, -- 当前页实际记录数 CASE MAX(row_nbr) OVER () WHEN total_rows THEN 'Y' ELSE 'N' END AS last_page, -- 是否最后一页 total_rows, -- 总记录数 row_nbr, -- 每一条数据的偏移量 ((row_nbr - 1) / 10) + 1 AS current_page -- 当前所在页码 FROM t ORDER BY emp_id;
首先,我们定义了通用表表达式 e,它是返回数据的初始查询,可以增加其他的过滤条件。
然后,我们基于 e 定义了另一个通用表表达式 t,在定义中进行了排序和分页,并且利用窗口函数 COUNT(*) 计算总的记录数,利用窗口函数 ROW_NUMBER () 计算每条数据的偏移量(行号)。
接下来,我们基于 t 返回了更多的参数,利用窗口函数 COUNT(*) 返回了当前页的实际记录数,通过窗口函数 MAX(row_nbr) 返回的当前页最大偏移量和总记录数的比较判断是否最后一页,以及当前所在的页码。
emp_id|emp_name|sex|email |actual_page_size|last_page|total_rows|row_nbr|current_page| ------+--------+---+-------------------+----------------+---------+----------+-------+------------+ 11|关平 |男 |guanping@shuguo.com| 10|N | 27| 11| 2| 12|赵氏 |女 |zhaoshi@shuguo.com | 10|N | 27| 12| 2| 13|关兴 |男 |guanxing@shuguo.com| 10|N | 27| 13| 2| 14|张苞 |男 |zhangbao@shuguo.com| 10|N | 27| 14| 2| 15|赵统 |男 |zhaotong@shuguo.com| 10|N | 27| 15| 2| 16|周仓 |男 |zhoucang@shuguo.com| 10|N | 27| 16| 2| 17|马岱 |男 |madai@shuguo.com | 10|N | 27| 17| 2| 18|法正 |男 |fazheng@shuguo.com | 10|N | 27| 18| 2| 19|庞统 |男 |pangtong@shuguo.com| 10|N | 27| 19| 2| 20|蒋琬 |男 |jiangwan@shuguo.com| 10|N | 27| 20| 2|
📝关于窗口函数的介绍可以参考这篇文章。
总结
本文介绍了如何利用窗口函数在一个语句中返回分页查询的结果和所需的全部参数,这种方法比传统的分页查询实现更加简洁高效。
- sqlserver 存储过程(SQL Server解析XML数据的方法详解)
- SQL中的注释
- mysql的uuid说明(MySQL GTID全面总结)
- 停止mysql服务命令(windows下实现定时重启Apache与MySQL方法)
- SqlServer 按时间段查询问题(SqlServer 按时间段查询问题)
- mysql数据备份的几种方式(MySQL数据库备份过程的注意事项)
- mysql主机双向复制配置(浅析MySQL并行复制)
- SQL Server ltrimrtrim函数的用法(SQL Server ltrimrtrim 去不掉空格的原因分析)
- mysql的浮点数类型(浅谈MySQL中float、double、decimal三个浮点类型的区别与总结)
- mysql深度分页问题(MySQL DDL 引发的同步延迟该如何解决)
- sql注入原理详细(Sql注入原理简介_动力节点Java学院整理)
- python怎么操作mysql(详解Python的数据库操作pymysql)
- 查看mysql支持的字符集(不可忽视的MySQL字符集)
- sql server2012自动备份(SQL SERVER 2012数据库自动备份的方法)
- sqlserver数据库中锁的4种类型(SQLSERVER对加密的存储过程、视图、触发器进行解密推荐)
- sqlserver数据库还原教程(SQL Server通过重建方式还原master数据库)
- 这里输入关键词(如何输入关键词)
- 熊猫中国国宝(熊猫国宝酒53酱香)
- 春节会放假几天(春节会放假吗)
- 小浴室,大民生 缙云3200多户困难群众洗上免费热水澡(小浴室大民生缙云3200多户困难群众洗上免费热水澡)
- 元旦闲谭(元旦闲谭)
- 息烽 这个村 治垃圾 有招 人人争当卫生模范(息烽这个村治垃圾)
热门推荐
- dockerpush被拒绝(docker pull拉取超时的解决方案)
- react动态创建菜单并实现局部刷新(使用react-virtualized实现图片动态高度长列表的问题)
- auto.js源码分享(最新热门脚本Autojs源码分享)
- C#中static静态变量的用法
- html如何设置图片高度和宽度(HTML如何让IMG自动适应DIV容器大小的实现方法)
- 阿里云mysql升级注意事项(阿里云mysql空间清理的方法)
- phpstudy详细教程学习(phpstudy的php版本自由修改的方法)
- vue点击事件动态禁用(vue开发移动端使用better-scroll时click事件失效的解决方案)
- sqlserver备份还原数据(通过Windows批处理命令执行SQL Server数据库备份)
- python中的迭代器详解(Python通过for循环理解迭代器和生成器实例详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9