🙏 我们需要您的帮助！SQLtest.online 没有广告，完全依靠捐款运行。哪怕一点点也很有帮助。 立即捐助 →
SQL 代码已复制到剪贴板

SQLtest.online

练习
课程
练习场
测试

SQL TOP、FETCH FIRST 和 WITH TIES

第 2.9 课 · 阅读时间：约 9 分钟 · 练习题 ↓

SQL TOP 和 FETCH FIRST n ROWS ONLY 是 LIMIT 的替代写法：TOP 用于 SQL Server，FETCH FIRST 则是 SQL 标准，PostgreSQL、Oracle、MariaDB 等数据库都支持。在本课中，你将学习标准语法、使用 OFFSET ... FETCH 分页、保留相同值行的 WITH TIES 选项、PERCENT，以及 Oracle 和 Firebird 的传统写法。

在第 2.7 课中，你使用 LIMIT 和 OFFSET 限制了查询结果。这种语法适用于 MySQL、MariaDB、PostgreSQL 和 SQLite，但 SQL Server 和 Oracle 不接受它。当你更换数据库、阅读别人的查询或准备面试时，会遇到表达同一意思的其他几种写法。

不同数据库中的行数限制：LIMIT、SQL Server 的 TOP 和标准的 FETCH FIRST；FETCH FIRST 3 ROWS WITH TIES 返回五部电影，因为另外两部的时长与最后一部相同

为什么限制行数有多种语法

很长一段时间里，限制行数并不属于 SQL 标准，于是每个数据库都发明了自己的语法：

  • MySQL 和 PostgreSQL 加入了 LIMIT，SQLite 沿用了它。
  • SQL Server 加入了 TOP。
  • Oracle 使用伪列 ROWNUM。
  • Firebird 加入了 FIRST 和 SKIP。

标准的 FETCH FIRST 子句直到 SQL:2008 才出现，那时这些习惯早已形成。如今大多数数据库在自有语法之外也支持标准形式，但 MySQL 和 SQLite 仍不支持。

FETCH FIRST n ROWS ONLY：标准语法

FETCH FIRST 写在 ORDER BY 之后，也就是 LIMIT 所在的位置：

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS ONLY;

结果：三部最短的电影，时长 46 分钟。该查询可在 MariaDB 10.6+、PostgreSQL 和 Oracle 中运行；MySQL 会报语法错误。

FIRST 与 NEXT、ROW 与 ROWS 可以互换：FETCH NEXT 1 ROW ONLY 与 FETCH FIRST 1 ROWS ONLY 含义相同，选读起来更自然的即可。省略数字时，FETCH FIRST ROW ONLY 返回一行。

使用 OFFSET ... FETCH NEXT 分页

OFFSET 的标准形式要带上 ROWS，并写在 FETCH 之前：

SELECT title, length
FROM film
ORDER BY length, title
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

结果：按时长排序的第 11 到第 15 部电影，与 LIMIT 5 OFFSET 10 相同。

这是 SQL Server 中唯一的分页方式，而且必须有 ORDER BY：没有排序的 OFFSET 是语法错误。

WITH TIES：保留与最后一行值相同的行

再看最短的电影。有五部电影时长都是 46 分钟，但 FETCH FIRST 3 ROWS ONLY 只返回其中三部，具体是哪三部由数据库决定。

WITH TIES 解决了这个问题：它返回前 N 行，再加上所有排序值与第 N 行相同的后续行。

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS WITH TIES;

结果：5 行。五部 46 分钟的电影全部返回，因为第三部电影的时长与另外两部相同。

选项行数得到的结果
FETCH FIRST 3 ROWS ONLY3五部 46 分钟电影中的任意三部
FETCH FIRST 3 ROWS WITH TIES5全部五部 46 分钟的电影

WITH TIES 能如实回答"最短的三部电影"这类问题：不会仅仅因为某部电影与最后一部的值相同就把它丢掉。

WITH TIES 的规则

  • 必须有 ORDER BY：是否"并列"由排序列决定。没有排序时，PostgreSQL 和 MariaDB 会报错。
  • 附加的排序列会消除并列。使用 ORDER BY length, title 时没有两行完全相同，WITH TIES 恰好返回 3 行。
  • 结果可能远多于 N 行。Sakila 中最长的电影都是 185 分钟，ORDER BY length DESC FETCH FIRST 3 ROWS WITH TIES 会返回 10 部电影。

在 PostgreSQL 的 Bookings 数据库中，同样的查询可以找出最长的航线：

SELECT flight_no, departure_city, arrival_city, duration
FROM routes
ORDER BY duration DESC
FETCH FIRST 3 ROWS WITH TIES;

结果：4 条时长 8 小时 50 分钟的航线：莫斯科与南萨哈林斯克或彼得罗巴甫洛夫斯克之间的航班。

PostgreSQL 13+、MariaDB 10.6+ 和 Oracle 12c+ 支持 FETCH 中的 WITH TIES。

SQL Server：TOP

SQL Server 把 TOP 写在 SELECT 之后：

SELECT TOP (3) Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

结果：AdventureWorks 中最贵的三件产品。

对于数字，括号可以省略（TOP 3 也能运行），但建议加上：有了括号，TOP 还能接受变量或表达式，例如 TOP (@n)。

TOP 有自己的 WITH TIES，而且只能和 TOP 一起使用：SQL Server 的 OFFSET ... FETCH 不支持 WITH TIES。

SELECT TOP (3) WITH TIES Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

结果：5 件产品。Road-150 Red 自行车的五个尺码价格都是 3578.27。

TOP 的陷阱

  • 允许不带 ORDER BY 使用 TOP，但会返回任意的行，和不排序的 LIMIT 一样。
  • 不带 ORDER BY 的 TOP ... WITH TIES 会报错。
  • SQL Server 不接受单独的 FETCH FIRST：FETCH 只能写在 OFFSET 之后。要取前几行，请写 OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY 或使用 TOP。

返回一定百分比的行

有时你需要的不是 10 行，而是 10% 的行。SQL Server 和 Oracle 可以直接做到：

SELECT TOP (1) PERCENT Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

结果：3 行。295 件产品的 1% 是 2.95，SQL Server 向上取整。

Oracle 使用标准形式：

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 PERCENT ROWS ONLY;

结果：薪资最高的 6 名员工：107 的 5% 是 5.35，向上取整。

DuckDB 把百分比写在 LIMIT 中：LIMIT 10%。PostgreSQL、MariaDB 和 MySQL 不支持百分比：需要先统计行数，再自己算出具体数字。

Oracle：ROWNUM 与 FETCH FIRST

12c 之前的 Oracle 没有 FETCH FIRST，旧代码使用伪列 ROWNUM 限制行数。它在读取行的同时编号，早于 ORDER BY 生效。因此下面的查询是错误的：

SELECT first_name, last_name, salary
FROM hr.employees
WHERE ROWNUM <= 3
ORDER BY salary;

结果：得到的不是最低的三个薪资，而是数据库最先读到的三行（17000、17000 和 24000），排序发生在筛选之后。

为了先排序，旧代码会把排好序的查询放进子查询，再对它的结果应用 ROWNUM：

SELECT first_name, last_name, salary
FROM (
    SELECT first_name, last_name, salary
    FROM hr.employees
    ORDER BY salary
)
WHERE ROWNUM <= 3;

结果：最低的三个薪资：2100、2200 和 2200。

另一个陷阱：WHERE ROWNUM > 1 一行也不返回。第一行得到编号 1，不满足条件被丢弃；下一行又成为编号 1，依此类推。

在 Oracle 12c 及更高版本中，直接使用 FETCH FIRST 即可，包括 WITH TIES：

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;

结果：8 名员工：第 7 和第 8 名的薪资都是 12008。

即使在 26ai 版本中，Oracle 仍然不接受 LIMIT。

Firebird：FIRST、SKIP 和 ROWS

Firebird 有两种自己的语法。FIRST 和 SKIP 像 TOP 一样写在 SELECT 之后：

SELECT FIRST 3 SKIP 2 first_name, last_name, salary
FROM employee
ORDER BY salary DESC;

结果：按薪资排序的第 3 到第 5 名员工：跳过两行，返回三行。

ROWS m TO n 写在查询末尾，接受的是行号而不是行数：

SELECT first_name, last_name, salary
FROM employee
ORDER BY salary DESC
ROWS 3 TO 5;

结果：同样是第 3 到第 5 名员工。单独的 ROWS 3 表示前三行。

从 Firebird 3.0 起，标准的 OFFSET ... FETCH 也可以使用。即使在 Firebird 5 中，也不支持 WITH TIES。

在 MySQL 和 SQLite 中实现 WITH TIES

MySQL 和 SQLite 既没有 FETCH，也没有 WITH TIES，但可以用课程后面会学到的工具得到相同的结果。这里先预览一下。

子查询找出第三行的值，外层查询取出所有不比它长的电影：

SELECT title, length
FROM film
WHERE length <= (
    SELECT length
    FROM film
    ORDER BY length
    LIMIT 1 OFFSET 2
)
ORDER BY length, title;

结果：与 FETCH FIRST 3 ROWS WITH TIES 相同的 5 部 46 分钟电影。子查询在第 6.2 课中讲解。

窗口函数 RANK() 给相同的值相同的排名，因此 rnk <= 3 会保留所有并列的行：

SELECT title, length
FROM (
    SELECT title, length, RANK() OVER (ORDER BY length) AS rnk
    FROM film
) AS ranked
WHERE rnk <= 3
ORDER BY length, title;

结果：同样的 5 部电影。RANK() 在第 7.2 课中讲解。

各数据库的行数限制语法

数据库前 N 行跳过行WITH TIES百分比
MySQLLIMIT nLIMIT n OFFSET m不支持不支持
MariaDB 10.6+LIMIT n、FETCH FIRST n ROWS ONLYLIMIT n OFFSET m、OFFSET m ROWS FETCH NEXT n ROWS ONLYFETCH ... WITH TIES不支持
PostgreSQL 13+LIMIT n、FETCH FIRST n ROWS ONLYLIMIT n OFFSET m、OFFSET m ROWS FETCH NEXT n ROWS ONLYFETCH ... WITH TIES不支持
SQL ServerTOP (n)OFFSET m ROWS FETCH NEXT n ROWS ONLYTOP (n) WITH TIESTOP (n) PERCENT
Oracle 12c+FETCH FIRST n ROWS ONLY（12c 之前为 ROWNUM）OFFSET m ROWS FETCH NEXT n ROWS ONLYFETCH ... WITH TIESFETCH FIRST n PERCENT ROWS ONLY
FirebirdFIRST n、ROWS n、FETCH FIRST（3.0+）SKIP m、ROWS m TO n、OFFSET ... FETCH（3.0+）不支持不支持
SQLiteLIMIT nLIMIT n OFFSET m不支持不支持
DuckDBLIMIT n、FETCH FIRST n ROWS ONLYLIMIT n OFFSET m、OFFSET ... FETCH不支持LIMIT n%

如果一个查询需要在多个数据库中运行，带 ORDER BY 的标准 OFFSET ... FETCH 覆盖面最广：除 MySQL 和 SQLite 之外都支持。学完本课，你就掌握了检索数据行的基础；下一个模块将介绍如何用 SQL 函数转换数据。

要点：

  • FETCH FIRST n ROWS ONLY 是 LIMIT 的标准替代写法；MySQL 和 SQLite 不支持。
  • OFFSET m ROWS FETCH NEXT n ROWS ONLY 用于分页；在 SQL Server 中必须有 ORDER BY。
  • WITH TIES 会加上与最后一行排序值相同的行，因此结果可能多于 N 行。
  • SQL Server 使用 TOP (n)、TOP (n) WITH TIES 和 TOP (n) PERCENT。
  • Oracle 的 ROWNUM 在 ORDER BY 之前分配：先在子查询中排序，或在 12c+ 中使用 FETCH FIRST。
  • 在 MySQL 和 SQLite 中，用子查询或 RANK() 实现 WITH TIES。

常见问题

SQL Server 中 LIMIT 的等价写法是什么？

取前几行用 SELECT TOP (n) ...；分页用 ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY。SQL Server 不接受 LIMIT，也不允许不带 OFFSET 的 FETCH。

SQL 中的 WITH TIES 有什么作用？

它返回前 N 行，再加上 ORDER BY 列的值与第 N 行相同的每一个后续行。FETCH FIRST 3 ROWS WITH TIES 可能返回 3 行、5 行或 10 行，取决于有多少行与第三个值相同。

MySQL 支持 FETCH FIRST 或 WITH TIES 吗？

不支持。MySQL 8 和 9 只支持 LIMIT。MariaDB 从 10.6 版本开始支持 FETCH FIRST ... WITH TIES。在 MySQL 中，可以用子查询或 RANK() 得到同样的结果。

如何在 Oracle 中限制行数？

在 Oracle 12c 及更高版本中，在 ORDER BY 之后使用 FETCH FIRST n ROWS ONLY。在旧版本中，先在子查询中排序，再用 WHERE ROWNUM <= n 过滤其结果。Oracle 不支持 LIMIT。

FETCH FIRST 和 FETCH NEXT 有什么区别？

没有区别：FIRST 和 NEXT 是同义词，ROW 和 ROWS 也是。按惯例，不带 OFFSET 时写 FETCH FIRST，在 OFFSET 之后写 FETCH NEXT。

尝试解决以下任务，以巩固您在本课中学到的内容。

  1. WITH TIES 返回多少行？
  2. 产品目录第 3 页
  3. SQL Server 中的前 5 行
  4. 最长的航线（含并列）
  5. 容量最大的教室（含并列）
上一课
继续练习
下一课