七、如何实现排行榜和前端分页效果

上一篇我们讨论了如何利用 ORDER BY 子句实现查询结果的排序。
对数据进行排序之后，还可以进一步进行处理。我们经常会看到各种 Top-N 排行榜，例如十大热门金曲、电影排行榜、游戏排行榜等。另外，在客户端显示数据时通常不是一次列出所有的结果，而是每次显示 N 条（10、20、50 等）记录；然后提供“下一页”、“上一页”等翻页功能。本篇我们就来了解一下如何使用 SQL 语句实现以上两种常见的功能。

Top-N 排行榜

Top-N 排行榜的原理就是先排序，再返回前 N 条记录。该过程的示意图如下：

实现 Top-N 排行榜的方式主要有两种：

• 标准 SQL 提供的 FETCH 语法；
• 另一种常见的 LIMIT 语法。

  使用标准 SQL 实现 Top-N 排行榜

  以下示例使用 FETCH 子句查询月薪排名前 5 的员工：

  -- Oracle、SQL Server 以及 PostgreSQL 实现
  SELECT emp_name, salary
  FROM employee
  ORDER BY salary DESC
  OFFSET 0 ROWS
  FETCH FIRST 5 ROWS ONLY;

  其中，ORDER BY 按照月薪从高到低进行排序；OFFSET 表示跳过 0 行；然后 FETCH 返回前 5 条数据，也就是月薪 Top-5 的员工。该语句的结果如下：

Oracle、SQL Server 以及 PostgreSQL 支持这种 FETCH 语法。Oracle 和 PostgreSQL 中的 OFFSET 可以省略，同样表示跳过 0 行数据。
接下来我们看看另一种语法：LIMIT。

使用 LIMIT 实现 Top-N 排行榜

MySQL 和 PostgreSQL 支持使用 LIMIT 替代 FETCH 实现相同的功能：

-- MySQL 以及 PostgreSQL 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 LIMIT 5 OFFSET 0;

其中，ORDER BY 按照月薪从高到低进行排序；OFFSET 表示跳过 0 行，LIMIT 返回前 5 条数据，也就是月薪 Top-5 的员工。另外，OFFSET 可以省略，同样表示跳过 0 行数据。该语句的结果与上面的 FETCH 示例相同。
MySQL 中以下两种语法形式等效，注意 10 和 5 出现的顺序：

-- 跳过 10 行，返回随后的 5 行数据
LIMIT 5 OFFSET 10
LIMIT 10, 5

理解了 Top-N 排行榜的原理，也就可以很容易地实现分页查询的效果。

分页查询

分页查询的原理就是先跳过指定的行数，再返回 Top-N 记录。分页查询的示意图如下：

分页查询与 Top-N 查询类似，也有两种实现方式。实际上，Top-N 查询是分页查询的一个特殊情况。

使用标准 SQL 实现分页查询

假设前端页面每次显示 5 条记录；当用户点击按钮显示第 3 页的数据，也就是第 11 条到第 15 条记录时，使用 FETCH 子句实现如下：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
OFFSET 10 ROWS
 FETCH FIRST 5 ROWS ONLY;

其中，ORDER BY 按照月薪从高到低进行排序；OFFSET 跳过 10 行；然后 FETCH 返回随后的 5 条数据。该语句的执行结果如下：

通过这种分页查询的方式，还可以实现其他功能。例如，以下语句可以找出哪些员工的月薪排名第 3 高：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT emp_name, salary
  FROM employee
 WHERE salary = (SELECT salary
                   FROM employee
                  ORDER BY salary DESC
                 OFFSET 2 ROWS
                  FETCH FIRST 1 ROWS ONLY);

首先，括号中是一个子查询，返回了第 3 高的月薪值（24000）；然后外部查询返回了月薪等于该值的所有员工。子查询将会在第 17 篇进行介绍，在这里我们可以将它看作一个常量 24000。该语句执行的结果如下：

现在，我们来看一下完整的 FETCH 语法：

[OFFSET M {ROW | ROWS}]
FETCH { FIRST | NEXT } [ num_rows | N PERCENT ] { ROW | ROWS } { ONLY | WITH TIES };

其中，方括号（[ ]）表示可选项；大括号（{ }）是必选项，竖线（|）表示可以二选一。每个参数的作用如下：

• OFFSET 表示偏移量，即从第 M+1 行开始返回；如果不指定，表示从第 1 行开始返回；ROW 和 ROWS 作用相同；
• FETCH 指定返回多少行，FIRST 和 NEXT 作用相同；
• num_rows 表示按照行数计算返回的数据量，N PERCENT 表示按照百分比计算返回的数据量，ROW 和 ROWS 作用相同；
• ONLY 和 WITH TIES 的差别在于，如果在最后有多个排名相同的数据行，WITH TIES 会返回更多的数据；默认为 ONLY。

目前只有 Oracle 12c 以上版本支持按照百分比计算行数和 WITH TIES 选项。以下示例按照百分比返回月薪最高的前 10% 员工：

-- Oracle 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 FETCH FIRST 10 PERCENT ROWS ONLY;

该语句返回的结果如下：

员工表中总共有 25 个人，百分之十约为 3 个人。
以下示例使用 WITH TIES 选项返回了更多的数据：

-- Oracle 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 FETCH FIRST 10 PERCENT ROWS WITH TIES;

该查询返回的结果如下：

由于“诸葛亮”和“张飞”的月薪相同，WITH TIES 返回了 4 条记录。
分页查询也可以使用 LIMIT 语法来实现。

使用 LIMIT 实现分页查询

使用 LIMIT 语法实现分页查询同样只需要修改 OFFSET 偏移量即可：

-- MySQL 以及 PostgreSQL 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 LIMIT 5 OFFSET 10;

其中，ORDER BY 按照月薪从高到低进行排序；OFFSET 跳过 10 行；然后 FETCH 返回随后的 5 条数据。该语句返回的结果与上一小节中的 FETCH 示例相同。

在开发应用程序时，我们只需要将偏移量（offset）和需要返回的行数（N）作为参数传递给 SQL 语句即可实现 Top-N 排行榜和分页查询。

除了常见的 FETCH 和 LIMIT 语法之外，有些数据库还可以通过其他的语法实现 Top-N 和分页查询的功能。

其他实现方式

SQL Server 实现了 TOP 语句，从名字就能看出它的作用。以下查询同样可以返回月薪排名前 5 的员工：

-- SQL Server 实现
SELECT TOP(5) emp_name, salary
  FROM employee
 ORDER BY salary DESC;

另外，SQL Server 中的 TOP 语句也支持 PERCENT 参数和 WITH TIES 选项，它们的作用和前文中的 Oracle 类似。
Oracle 11g 或者更早的版本无法使用 FETCH 语句， 但是可以使用子查询和 ROWNUM 函数实现相同的功能：

-- Oracle 实现
SELECT emp_name, salary
  FROM (SELECT emp_name, salary, rownum AS rn
          FROM (SELECT emp_name, salary
                  FROM employee
                 ORDER BY salary DESC
               )
       )
 WHERE rn >= 11 AND rn <= 15;

简单来说，最里面包含 ORDER BY 的子查询将员工按照月薪从高到低进行排序；中间的 SELECT 语句使用 ROWNUM 函数为排序后的数据指定一个编号；最外层的查询返回编号从 11 到 15 的数据。我们将会在第 17 篇介绍子查询。

小结

查询语句中的 FETCH 和 OFFSET 子句可以限定返回结果的数量和偏移量，从而实现排行榜和分页查询效果。LIMIT 和 OFFSET 子句也是实现该功能的一种常见的用法。另外，某些数据库还实现了其他的替代方式。