在传统的实现方法中我们需要执行额外的查询语句获得这些信息，本文介绍一种只需要一个查询语句就可以返回所有数据的方法，也就是通过 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 分页查询时需要哪些参数：
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|
这种方法比传统的分页查询实现更加简洁高效。
