sql-top-limit-or-rownum-clause

SQL - 限制结果集

在查询大型表时，您通常只需要获取一部分行，例如前 N 条记录或特定“页”的结果。SQL 标准提供了实现此功能的方法，但许多流行的数据库最初实现了自己的非标准子句。

标准 SQL: FETCH FIRST 子句

ANSI SQL 标准（自 SQL:2008 起）提供了 FETCH FIRST N ROWS ONLY 子句（仅获取前 N 行）。它与 ORDER BY 子句结合使用，以确保您获得可预测、稳定的结果子集。

语法

SELECT column_name(s)
FROM table_name
WHERE [condition]
ORDER BY column_name
FETCH FIRST number ROWS ONLY;

PostgreSQL、Oracle 和 DB2 的现代版本都支持此语法。

常见供应商特定子句

尽管标准已存在，但在现有代码库中您仍会经常遇到特定于供应商的语法。

MySQL / PostgreSQL: LIMIT 和 OFFSET

MySQL 和 PostgreSQL 使用 LIMIT 子句。它通常与 OFFSET 子句（偏移量）搭配用于分页。

-- 获取前 N 行
SELECT * FROM table_name ORDER BY column DESC LIMIT 10;

-- 获取 10 行，从第 21 行开始（如果每页大小为 10，则为第 3 页）
SELECT * FROM table_name ORDER BY column DESC LIMIT 10 OFFSET 20;

SQL Server: TOP

SQL Server 使用 TOP 子句（顶部/前 N 行）。要在现代版本中实现分页，您必须使用更详细的 OFFSET ... FETCH 语法。

-- 获取前 N 行
SELECT TOP 10 * FROM table_name ORDER BY column DESC;

-- 现代 SQL Server 中的分页
SELECT * FROM table_name ORDER BY column DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle (旧版): ROWNUM

较旧的 Oracle 版本使用一个名为 ROWNUM 的伪列（行号伪列）。这种语法以其棘手而闻名，并已被标准的 FETCH FIRST 子句取代。

-- 旧版 Oracle 获取前 N 行的语法
SELECT * FROM (
    SELECT * FROM table_name ORDER BY column DESC
) WHERE ROWNUM <= 10;

示例：获取薪资最高的前 3 名客户

让我们使用 Customers 表。我们希望找到薪资最高的前 3 名客户。

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

关键在于 始终使用 ORDER BY 子句来定义“顶部”的含义。

MySQL / PostgreSQL 示例

SELECT ID, Name, Salary
FROM Customers
ORDER BY Salary DESC
LIMIT 3;

SQL Server 示例

SELECT TOP 3 ID, Name, Salary
FROM Customers
ORDER BY Salary DESC;

标准 SQL / 现代 Oracle 示例

SELECT ID, Name, Salary
FROM Customers
ORDER BY Salary DESC
FETCH FIRST 3 ROWS ONLY;

所有三个查询都将产生相同的正确结果：

+----+--------+----------+
| ID | Name   | Salary   |
+----+--------+----------+
|  7 | Muffy  | 10000.00 |
|  5 | Hardik |  8500.00 ||  4 | Chaitali |  6500.00 |
+----+--------+----------+