SQL如何提取分组内最长运行记录_ROW_NUMBER与排序-SQL-PHP中文网

ROW_NUMBER() 必须配合 ORDER BY 才能确定“最长运行”，否则无意义；正确写法是 ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY duration_sec DESC) AS rn，再外层 WHERE rn = 1 筛选，注意 NULL 处理、索引优化及数据库兼容性。

sql如何提取分组内最长运行记录_row_number与排序

ROW_NUMBER() 必须配合 ORDER BY 才能确定“最长运行”

不写 ORDER BY 的 ROW_NUMBER() 没有意义——它不会自动按运行时长排序，更不会知道你要“最长”。数据库只按你明确写的排序逻辑给序号。所谓“最长运行记录”，本质是先按运行时长降序，再取每组第一条。

常见错误现象：ROW_NUMBER() OVER (PARTITION BY job_id) 缺少 ORDER BY duration DESC，结果随机取一条，根本不可靠。

使用场景：日志表里有 job_id、start_time、end_time，想查每个任务中耗时最久的一次执行
正确写法必须包含 ORDER BY (end_time - start_time) DESC 或等效的时长字段（如已有 duration_sec）
注意 NULL 值：如果 end_time 可能为 NULL，end_time - start_time 会得 NULL，排在最前或最后取决于数据库，默认可能不是你想要的；建议加 WHERE end_time IS NOT NULL 预过滤

用 WHERE rn = 1 筛选时，别漏掉外层查询

ROW_NUMBER() 是窗口函数，不能直接在 WHERE 里用。必须套一层子查询或 CTE，否则报错 column "rn" does not exist。

实操建议：

最简写法用子查询：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY job_id ORDER BY duration_sec DESC
  ) AS rn
  FROM job_log
) t WHERE rn = 1;

别把 ORDER BY 放在外层——外层 ORDER BY 不影响 ROW_NUMBER() 计算顺序，只是最终输出排序，和“取最长”无关
如果表很大，PARTITION BY 字段没索引，性能会明显下降；建议在 (job_id, duration_sec) 上建复合索引

ROW_NUMBER() vs MAX() + JOIN：选哪个？

有人用 MAX(duration_sec) 先聚合，再跟原表 JOIN 回去，也能拿到最长记录。但两者行为不同。

ROW_NUMBER()：严格返回每组一条，即使有多条并列最长，也只随机（按物理顺序）取一条
MAX() + JOIN：会把所有并列最长的都返回，数量不确定
如果你要“任选一条最长”，ROW_NUMBER() 更可控；如果要“所有最长”，就得换思路，比如用 RANK() 或 DENSE_RANK()
RANK() 并列会跳号（1,1,3），DENSE_RANK() 不跳（1,1,2），但二者都返回全部并列项；而 ROW_NUMBER() 永远不并列

PostgreSQL / MySQL 8.0+ / SQL Server 都支持，但旧版 MySQL 不行

MySQL 5.7 及更早版本不支持窗口函数，强行写 ROW_NUMBER() 会报错 FUNCTION xxx.ROW_NUMBER does not exist。

替代方案只有自连接或变量模拟，但极易出错：

变量方式（MySQL 5.7）依赖执行顺序，无索引时结果不稳定
自连接写法复杂、性能差，特别是数据量大时
如果无法升级，优先考虑应用层分组后排序取首条，比硬写 SQL 更可靠
SQL Server 2005+、PostgreSQL 8.4+、Oracle 8i+、SQLite 3.25+、MySQL 8.0+ 均原生支持，无需额外配置

真正容易被忽略的是：不同数据库对 NULL 在 ORDER BY 中的默认位置处理不一致（PostgreSQL 默认 NULLS LAST，MySQL 默认 NULLS FIRST），如果时长字段含 NULL，又没显式声明 NULLS LAST，可能把未结束的任务误判为“最长”。