SQL 结果集分页

SQL 结果集分页,你想对结果集进行分页,或者“滚动浏览”一组结果集。例如,你希望从 EMP 表返回最前面的 5 条工资记录,然后返回接下来的 5 条,等等。你的目的是让用户一次看到 5 条记录,并在每次点击“下一页”按钮之后变换显示内容。

SQL 结果集分页 问题描述

你想对结果集进行分页,或者“滚动浏览”一组结果集。例如,你希望从 EMP 表返回最前面的 5 条工资记录,然后返回接下来的 5 条,等等。你的目的是让用户一次看到 5 条记录,并在每次点击“下一页”按钮之后变换显示内容。

SQL 结果集分页 解决方案

SQL 里并没有“最先”“最后”或“下一个”这样的概念,我们必须对行记录做出明确的排序。只有做过了排序,才有可能准确地从结果集中返回指定区间的记录。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER 实现排序,并且在 WHERE 子句中指定我们希望返回的行。例如,返回第 1 到第 5 行。

select sal
  from (
select row_number() over (order by sal) as rn,
       sal
  from emp
       ) x
 where rn between 1 and 5
 
 SAL
----
 800
950
1100
1250
1250

然后,返回第 6 行到第 10 行。

select sal
  from (
select row_number() over (order by sal) as rn,
       sal
  from emp
       ) x
 where rn between 6 and 10
 
 SAL
-----
 1300
 1500
 1600
 2450
 2850

通过改变 WHERE 子句,我们能返回任意区间内的行。
MySQLPostgreSQL
对于这两种数据库而言,滚动结果集非常容易,因为它们支持 LIMITOFFSET 子句。使用 LIMIT 子句指定要返回的行数,使用 OFFSET 子句指定要跳过的行数。例如,按照工资排序返回最前面的 5 行。

select sal
  from emp
 order by sal limit 5 offset 0
 
   SAL
------
   800
   950
  1100
  1250
  1250

然后,返回接下来的 5 行。

select sal
  from emp
 order by sal limit 5 offset 5
 
  SAL
-----
 1300
 1500
 1600
 2450
 2850

LIMITOFFSET 子句使得 MySQLPostgreSQL 解决方案的代码变得更简单,而且更具可读性。

SQL 结果集分页 扩展知识

内嵌视图 X 里的窗口函数 ROW_NUMBER OVER 将会为每一行工资记录分配一个唯一的数字编号(从 1 开始递增)。下面是内嵌视图 X 的查询结果集。

select row_number() over (order by sal) as rn,
       sal
  from emp
 
RN        SAL
-- ----------
 1        800
 2        950
 3       1100
 4       1250
 5       1250
 6       1300
 7       1500
 8       1600
 9       2450
10       2850
11       2975
12       3000
13       3000
14       5000

一旦每一行工资记录都被指定了数字编号,通过指定 RN 的值就可以筛选出我们想要返回的区间。
对于 Oracle 用户来说,有一个替代方案:可以用 ROWNUM 函数来代替 ROW_NUMBER OVER 函数,同样能为每一行记录生成一个序号。

select sal
  from (
select sal, rownum rn
  from (
select sal
  from emp
 order by sal
       )
       )
 where rn between 6 and 10
 
  SAL
-----
 1300
 1500
 1600
 2450
 2850

使用 ROWNUM 的话,就需要多写一层子查询。最内层的子查询对工资进行排序。接下来的外层子查询为每一行分配序号。最后,最外层的 SELECT 返回我们希望显示的数据。
MySQL 和 PostgreSQL
SELECT 里的 OFFSET 子句使得整个查询语句看起来更加直观,更具可读性。OFFSET 等于 0 表示将从第 1 行开始读取,OFFSET 等于 5 表示从第 6 行开始,OFFSET 等于 10 表示从第 11 行开始。LIMIT 子句则限定了返回的记录行数。通过结合这两种子句,我们就能很容易地在结果集中指定从哪一行开始,并同时指定返回多少行。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程