如何用高效单SQL查询获取同area下指定number的下一行数据

先明确需求：通过number定位行后，获取同area内order值更大的最小行的number。以下是两种高效的单查询方案：

方案1：JOIN关联查询（兼容多数数据库） #

通过JOIN一次性获取目标行的area和order，再关联查询符合条件的下一行，避免了两次子查询的重复检索：

SELECT t2.number
FROM <table-name> t1
JOIN <table-name> t2 
  ON t2.area = t1.area 
  AND t2.`order` > t1.`order`
WHERE t1.number = 222
ORDER BY t2.`order`
LIMIT 1;

优势 #

• 仅需检索一次目标行，比原双子女查询减少一次数据库访问
• 兼容性强，支持MySQL 5.x、PostgreSQL、SQL Server等多数数据库

方案2：窗口函数查询（适合支持窗口函数的数据库） #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL 9.4+、SQL Server 2012+），可以用LEAD()函数直接预计算每行的下一行数据，查询效率更高：

WITH ranked_data AS (
  SELECT 
    number,
    LEAD(number) OVER (PARTITION BY area ORDER BY `order`) AS next_number
  FROM <table-name>
)
SELECT next_number
FROM ranked_data
WHERE number = 222;

优势 #

• 仅扫描一次全表，若需批量查询多个number的下一行，无需重复扫表，效率远高于多次单查询
• 逻辑清晰，直接通过窗口函数生成每行的下一行关联数据

索引优化建议 #

为进一步提升查询效率，建议创建两个复合索引：

• 索引idx_number_area_order (number, area, order)：快速定位目标行
• 索引idx_area_order_number (area, order, number)：让过滤、排序和取值直接通过索引完成，无需回表

内容的提问来源于stack exchange，提问作者Chanda Siskin