不闻不若闻之,闻之不若见之,见之不若知之,知之不若行之。学至于行之而止矣。——荀子
大家好!我是只谈技术不剪发的 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|
关于窗口函数的介绍可以参考这篇文章。
本文介绍了如何利用窗口函数在一个语句中返回分页查询的结果和所需的全部参数,这种方法比传统的分页查询实现更加简洁高效。
如果你觉得文章有用,欢迎评论、点赞、推荐
文章浏览阅读3.5k次。函数体内部的语句在执行时,一旦执行到return,函数就执行完毕,并将结果返回。如果没有return语句,函数执行完毕后也会返回结果,只是结果为None。return None可以简写为return。Python中的函数是可以返回多个值的。比如在游戏中经常需要从一个点移动到另一个点,给出坐标、位移和角度,就可以计算出新的新的坐标:import mathdef move(x, y, step, an..._python 自定义函数中,return可以返回多个值
文章浏览阅读9.2k次。橡皮擦具体功能要求:1. 实现擦除效果:具体要求是点击位置,拖动轨迹路上,均可以擦除。在快速拖动过程中,不能出现断层和锯齿现象。2. 擦除的形状,最好可以自定义。默认可以提供正方形、圆形两种,最好能提供自定义图片形状。3. 判断图片是否擦除完毕。4. 如果擦除形状过小,那么难免在擦除过程中,会遗留一些细小的、可能难以注意的残留点。在擦除过程中,要求可以自动擦除这些残留点。_cocos 判断橡皮擦是否擦干净了
文章浏览阅读1.1w次,点赞5次,收藏46次。#include#include#define FALSE 0#define TRUE 1#define ERROR 0#define OK 1#define OVERFLOW -2#define STACK_INIT_SIZE 100#define STACKINCREMENT 10typedef int status;//二叉树的二叉链表存
文章浏览阅读796次,点赞22次,收藏20次。本系统带文档lw万字以上文末可领取本课题的JAVA源码参考。
文章浏览阅读10w+次,点赞209次,收藏1.3k次。确定串口名称WINDOWS#!/usr/bin/env python#-*- coding: utf-8 -*import serialimport serial.tools.list_portsplist = list(serial.tools.list_ports.comports())if len(plist) <= 0: print ("The Serial..._python serial
文章浏览阅读3.8k次。参考参考网址2-CH CAN FD HAT - Waveshare Wikihttps://www.waveshare.net/wiki/2-CH_CAN_FD_HAT特此记录anlog2021年11月14日_树莓派4b供电方案
文章浏览阅读2k次。解决用Xftp向虚拟机VMware传文件速度慢的问题_往虚拟机传文件哪个软件快
文章浏览阅读202次。ADAU1701的Soundbar程序详解_soundbar开发
文章浏览阅读882次。我的目录是:C:\Users\Administrator\Desktop\driving\yolov5-mask-42-master\runs\train\exp_yolov5s\weights里的best.pt。这样做是为了防止移植到Android端,检测时出现密密麻麻的检测框覆盖图片。转为onnx格式后,还需要再进行模型简化,不然会在转换的时候报错。查看网络的总体架构,如果不想安装到本地,可以直接点击。,选择刚才转换出来的best.onnx文件即可。4、ONNX转param、bin。_pt转bin
文章浏览阅读2.1k次。本篇记录了创建mavenweb空项目过程。步骤:打开Eclipse-》菜单file-》new-》other项目创建完毕会报错,因为webapp没有文件,右键项目名称-》Properties,选中Project Facets,钩掉Dynamic Web Module,点击OK再次 右键项目名称-》Properties,选中Project Facets,钩上Dynami..._创建mavenweb项目version是空的
文章浏览阅读1.3w次,点赞39次,收藏130次。Render Hell – Book I如今对美术师的要求越来越高,因为在计算机眼里,他们提供的资源(asset)不过是一堆 **顶点** 和 **纹理** 数据的集合而已。而将这些数据转换为最终的图像,则主要是通过计算机中的 CPU 和 GPU 来完成的。_史上最通俗易懂的gpu入门教程
文章浏览阅读1.6k次。今天在用新版本cocos2dx.3.17版本VS用2017时,在进行运行启动本地windows时出现脚本错误。在网上看了很多的解决办法。都没有解决我的问题。最后大家都说是浏览器版本不支持的原因。我的系统默认IE游览器是IE9,然后我升级到IE11最新版。在运行时,就没有出现错误信息了、。所以,升级你的浏览器 升级你的浏览器 问题就解决了..._vs2017中的本地windows调试器使用不了怎么办