SQL 提取最靠前的n行记录|极客教程

SQL 提取最靠前的n行记录，你想基于某种排序方式从结果集中提取出限定数目的记录。例如，你希望返回 5 个工资最高的员工的姓名和工资。

SQL 提取最靠前的n行记录问题描述

你想基于某种排序方式从结果集中提取出限定数目的记录。例如，你希望返回 5 个工资最高的员工的姓名和工资。

SQL 提取最靠前的n行记录解决方案

本解决方案的关键之处有两点：首先要基于我们感兴趣的列对数据集进行排序，然后从结果集里提取出所需数目的行记录。
DB2、Oracle 和 SQL Server
本解决方案需要用到窗口函数。使用哪个窗口函数取决于我们希望如何处理 Tie1。下面的解决方案选择了 DENSE_RANK 函数，这意味着每一个 Tie 只会被计数一次。

1  select ename,sal
2    from (
3  select ename, sal,
4         dense_rank() over (order by sal desc) dr
5    from emp
6         ) x
7   where dr <= 5

上述查询返回的行数可能超过 5，但只有 5 种不同的工资值。如果你希望不考虑 Tie，只返回 5 行记录的话，那就使用 ROW_NUMBER OVER（因为该函数不关心 Tie）。
MySQL 和 PostgreSQL
使用标量子查询为每个工资值创建一个序号，然后通过上述序号限制子查询的结果行数。

 1  select ename,sal
 2    from (
 3  select (select count(distinct b.sal)
 4            from emp b
 5           where a.sal <= b.sal) as rnk,
 6          a.sal,
 7          a.ename
 8    from emp a
 9         )
10   where rnk <= 5

SQL 提取最靠前的n行记录扩展知识

DB2、Oracle 和 SQL Server
内嵌视图 X 里的窗口函数 DENSE_RANK OVER 完成了全部工作，执行该函数后得到的结果如下所示。

select ename, sal,
       dense_rank() over (order by sal desc) dr
  from emp
　
ENAME      SAL         DR
------- ------ ----------
KING      5000          1
SCOTT     3000          2
FORD      3000          2
JONES     2975          3
BLAKE     2850          4
CLARK     2450          5
ALLEN     1600          6
TURNER    1500          7
MILLER    1300          8
WARD      1250          9
MARTIN    1250          9
ADAMS     1100         10
JAMES      950         11
SMITH      800         12

最后，我们只需要返回 DR 小于或等于 5 的行即可。
MySQL 和 PostgreSQL
内嵌视图 X 里的标量子查询为工资值编排序号，结果如下。

select (select count(distinct b.sal)
          from emp b
         where a.sal <= b.sal) as rnk,
       a.sal,
       a.ename
  from emp a
　
RNK    SAL ENAME
--- ------ -------
  1   5000 KING
  2   3000 SCOTT
  2   3000 FORD
  3   2975 JONES
  4   2850 BLAKE
  5   2450 CLARK
  6   1600 ALLEN
  7   1500 TURNER
  8   1300 MILLER
  9   1250 WARD
  9   1250 MARTIN
 10   1100 ADAMS
 11    950 JAMES
 12    800 SMITH

最后，返回 RNK 小于或等于 5 的那些行即可。

1此处 Tie 意为“平手、平局”。本书保持不译。在排序计算的过程中，如果一个名次上出现了多个候选项，则每一个候选项均可称之为“一个 Tie”。有的数据库函数的计算结果中允许出现多个 Tie，有的则仅返回一个。——译者注