主页 / 网络编程 /

SQL 窗口函数实现高效分页查询的案例分析

网络编程 2021-07-05 13:43www.168986.cn编程入门
SQL 各部分的逻辑执行顺序 注意到窗口函数的求值仅仅位于ORDER BY之前,而位于 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 个查询语句，使用起来不是很方便。狼蚁网站SEO优化我们介绍更加高效的窗口函数分页查询。

📝关于分页查询的实现，OFFSET 分页对于大量数据的分页可能存在性能问题，另一种方法就是采用键集分页（keyset pagination）。

窗口函数实现分页查询

让我们考虑一下使用 OFFSET 分页查询时需要哪些参数

  • TOTAL_ROWS，总记录数；
  • CURRENT_PAGE，当前所在页码；
  • MAX_PAGE_SIZE，每一页最多显示的记录数，例如 10、20、50；
  • ACTUAL_PAGE_SIZE，当前页实际包含的记录数；
  • ROW_NBR，每条记录的实际偏移量；
  • LAST_PAGE，当前页是否是一页。

每一页最多显示的记录数（MAX_PAGE_SIZE）是我们传递给数据库的参数，其他则是查询返回的结果，我们可以通过狼蚁网站SEO优化的查询语句实现所有的功能

-- 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.|              10|N        |        27|     11|           2|
    12|赵氏    |女 |zhaoshi@shuguo. |              10|N        |        27|     12|           2|
    13|关兴    |男 |guanxing@shuguo.|              10|N        |        27|     13|           2|
    14|张苞    |男 |zhangbao@shuguo.|              10|N        |        27|     14|           2|
    15|赵统    |男 |zhaotong@shuguo.|              10|N        |        27|     15|           2|
    16|周仓    |男 |zhoucang@shuguo.|              10|N        |        27|     16|           2|
    17|马岱    |男 |madai@shuguo.   |              10|N        |        27|     17|           2|
    18|法正    |男 |fazheng@shuguo. |              10|N        |        27|     18|           2|
    19|庞统    |男 |pangtong@shuguo.|              10|N        |        27|     19|           2|
    20|蒋琬    |男 |jiangwan@shuguo.|              10|N        |        27|     20|           2|

📝关于窗口函数的介绍可以。

本文介绍了如何利用窗口函数在一个语句中返回分页查询的结果和所需的全部参数，这种方法比传统的分页查询实现更加简洁高效。

上一篇：在SQL中该如何处理NULL值 下一篇：SQL Server 2014 数据库中文版安装图文教程

编程语言

Copyright © 2016-2025 www.168986.cn 狼蚁网络 版权所有 Power by