不闻不若闻之,闻之不若见之,见之不若知之,知之不若行之。学至于行之而止矣。——荀子
大家好!我是只谈技术不剪发的 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 分页查询时需要哪些参数:
每一页最多显示的记录数(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|
关于窗口函数的介绍可以参考这篇文章。
本文介绍了如何利用窗口函数在一个语句中返回分页查询的结果和所需的全部参数,这种方法比传统的分页查询实现更加简洁高效。
如果你觉得文章有用,欢迎评论、点赞、推荐
文章浏览阅读497次。AOP实现权限拦截注解名称:CheckUnSysAdmin注解实现类:CommonAspectController层方法上引入注解名称:CheckUnSysAdminpackage com.sf.XWFS.aop;import java.lang.annotation.*;/** * @author cc * Desc 校验除超管外的角色,都进行拦截 */@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType_apo拦截控制层
文章浏览阅读52次。时隔4个月后,瑞星杀毒造假案又有了戏剧性的变化。近日,瑞星杀毒造假案的主角——北京市公安局网监处原处长于兵的二审结果仍维持一审的死缓判决。而据于兵的最新供认资料,相当一部分病毒是杀毒软件公司自己的科技力量研制的。于兵供认,瑞星公司向其行贿时就提出条件,由公安机关发出病毒警报,提示用户下载该公司杀毒软件进行杀毒,而病毒则是由瑞星公司“研制”的。“其实这是杀毒软件行业里的公开秘密。”国内一家知名...
文章浏览阅读6k次,点赞4次,收藏35次。考试重点1. 密码体制分类对称密码体制和非对称密码体制;2. DES和AES算法的特点(结构、密钥长度,分组长度,DES弱密钥)及其过程(置换过程,S盒查表过程),AES的轮结构DESDES结构首先是一个初始置换IP,用于重排明文分组的64比特;相同功能的16轮变换,每轮都有置换和代换;第16轮的输出分为左右两半并被交换次序;最后经过一个逆初始置换产生64比特密文;DES结构图如下:密钥长度:56分组长度:64DES弱密钥:待续了解即可DES 分组长度_移位密码和vigenere密码的异同是什么
文章浏览阅读2.7w次,点赞97次,收藏158次。系统功能包括管理员服务端:首页、轮播图管理、公告信息管理、系统用户(管理员、租客用户、房主用户)资源管理(新闻列表、新闻分类列表)模块管理(房源信息、房源咨询、租赁申请、入住信息、房租信息、反馈信息、通知信息、房屋类型)个人管理;用户客户端:首页、公告信息、新闻资讯、房源信息等功能。_微信小程序租房平台怎么弄
文章浏览阅读417次。文章目录一、禁止鼠标右键菜单二、禁止鼠标选中三、鼠标事件对象四、鼠标事件对象clientXpageXscreenX五、常用键盘事件一、禁止鼠标右键菜单<body> <script> document.addEventListener('contextmenu', function (e) { e.preventDefault(); }) </script></body>二、禁止_js鼠标点击事件菜鸟教程
文章浏览阅读7.1k次,点赞15次,收藏100次。PID算法中位置环与位置速度双环的对比分析_偏位置环控制速度
文章浏览阅读235次。A Key Volume Mining Deep Framework for Action Recognition_a key volume mining deep framework for action recognition
文章浏览阅读3.9k次。广告关闭腾讯云11.11云上盛惠 ,精选热门产品助力上云,云服务器首年88元起,买的越多返的越多,最高返5000元!2、python生成目录树上述 cmd 方式虽然可以生成目录树,但是并不美观,让我们用 python 实现。 2.1 标准库pathlib介绍python有一个标准文件路径处理库 os.path ,从 python3.4 开始,python 又加入了一个标准库 pathlib ,该库..._python创建一个窗口
文章浏览阅读5.1k次,点赞5次,收藏10次。时序图(Sequence Diagram)是显示对象之间交互的图,这些对象是按时间顺序排列的。顺序图中显示的是参与交互的对象及其对象之间消息交互的顺序。时序图中包括的建模元素主要有:角色(Actor)、对象(Object)、生命线(Lifeline)、控制焦点(Focus of control)/ 激活(Activation)、消息(Message)、组合片段(Combined Fragments_使用powerdesiger 画出时序图有接口 控制
文章浏览阅读1.2k次。文章目录一. 动态分区概述1.1 原理1.2 使用方式1.3 动态分区规则参数1.4 创建历史分区规则1.5 注意事项二. 案例2.1 案例12.2 案例22.3 案例3参考:一. 动态分区概述动态分区是在 Doris 0.12 版本中引入的新功能。旨在对表级别的分区实现生命周期管理(TTL),减少用户的使用负担。目前实现了动态添加分区及动态删除分区的功能。动态分区只支持 Range 分区。名词解释:FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。BE:Backend_dynamic_partition.history_partition_num
文章浏览阅读309次。笔记_禅道的运行日志放在哪
文章浏览阅读4.3w次,点赞79次,收藏882次。我们要使用这些知识实现一个简单的网页设计,利用HTML的a标签做文本内容跳转以及超链接的应用,CSS设计内容样式和图片、动画、视频的大小位置格式,JavaScript实现轮播图效果等。学习如何设计网页中的轮播图和动画效果,并掌握a标签文本内容跳转、超链接的应用、播放音乐与视频等操作。通过对Web知识内容的了解,我们掌握了HTML、CSS和JavaScript的基本知识以及利用它们实现一些简单的应用。1、使用Web知识实现一个简单的网页设计,利用HTML的a标签做文本内容跳转以及超链接的应用。_web前端网页设计代码