sql-top-clause

SQL - 限制结果集

处理大型表时，你通常不需要检索每一行。限制查询返回的行数对于性能、分页和 Top-N 分析（例如查找“最畅销的 10 个产品”）至关重要。虽然不同的数据库系统历来使用不同的语法，但 SQL 标准现在提供了一种统一的方法。

SQL 标准：FETCH FIRST 子句

SQL:2008 中引入的 FETCH FIRST N ROWS ONLY 子句是限制查询结果的标准、可移植方式。它通常与 ORDER BY 一起使用，以确保结果有意义且是确定性的。许多现代数据库，如 PostgreSQL、Oracle 和 DB2 都支持此语法。

语法

SELECT column_list
FROM table_name
ORDER BY sort_expression
OFFSET M ROWS
FETCH FIRST N ROWS ONLY;

• ORDER BY：对于定义哪些行是“前几行”至关重要。没有它，返回的行是任意的。
• OFFSET M ROWS：可选。在开始获取之前跳过前 M 行。
• FETCH FIRST N ROWS ONLY：指定要返回的最大行数。

示例：查找最贵的 5 个产品

使用 products 表：

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

厂商特定语法：LIMIT 和 TOP

虽然新编写的可移植代码优先使用标准语法，但在现有项目和不同的数据库系统中，你仍会经常遇到厂商特定语法。

MySQL 和 PostgreSQL：LIMIT

这些流行的数据库使用 LIMIT 子句，它非常简洁。

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

SQL Server：TOP

SQL Server 在 SELECT 语句中使用 TOP(N) 子句。请注意，现代 SQL Server 也支持标准的 OFFSET/FETCH 语法，这通常是分页的首选。

SELECT TOP(5) name, price
FROM products
ORDER BY price DESC;

实现分页

分页——将结果分页面显示（例如，每页 10 项）——是限制结果的主要用例。这通过结合限制和偏移量来实现。

示例：获取产品表的第 3 页（每页 10 条）

要获取第三页，我们需要跳过前两页（2 * 10 = 20 行），然后获取接下来的 10 行。

标准 SQL / PostgreSQL / SQL Server (现代)：

SELECT name, price
FROM products
ORDER BY name
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

MySQL：

SELECT name, price
FROM products
ORDER BY name
LIMIT 10 OFFSET 20;

获取包含并列项的行

如果你要求获取最贵的 5 个产品，但第 5 个和第 6 个产品价格完全相同怎么办？默认情况下，数据库会任意选择一个作为第 5 个。WITH TIES 选项通过包含所有在 ORDER BY 列中与最后一行具有相同值的行来解决这个问题。

示例

如果你想按价格获取最贵的 5 个产品，但同时也想包含所有与第 5 个产品价格相同的其他产品，你可以使用：

-- 标准 SQL / SQL Server
SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS WITH TIES;

如果多个产品的价格并列第五高，此查询可能会返回 6 或 7 行。

性能与最佳实践

• 始终使用 ORDER BY：没有确定性顺序地限制结果通常是一个错误，因为每次运行查询时你可能会得到不同的行。
• 为性能创建索引：为了使 ORDER BY ... LIMIT 查询快速执行，你必须在排序的列上创建索引。没有索引，数据库可能需要对整个表进行排序才能选择出顶部行，这在大型表上会非常慢。
• 键集分页：对于非常大的数据集，基于 OFFSET 的分页在深层页面（例如，第 50,000 页）上会变慢，因为数据库仍然需要扫描并计数所有要跳过的行。一种更高级的技术称为“键集分页”（keyset pagination）或“查找方法”（seek method），可以通过在有序列上使用 WHERE 子句（例如，WHERE created_at > [last_timestamp_from_previous_page]）来获得更好的性能。
• 限制 DELETE 或 UPDATE：虽然某些数据库允许限制 DELETE 或 UPDATE 语句（例如，DELETE ... LIMIT 100），但这可能存在风险。更安全的模式是将你想要操作的行的主键 SELECT 到一个临时列表或表中，然后将该列表用于 DELETE/UPDATE 语句的 WHERE 子句中（例如，DELETE FROM table WHERE id IN (subquery)）。