怎么在SQL中编写子查询获取分组内最新状态变更记录-SQL-PHP中文网

必须用row_number() over (partition by order_id order by updated_at desc, id desc)生成序号，再通过子查询或cte外层筛选rn = 1；直接group by无法返回整行，且mysql 5.7及更早不支持窗口函数。

怎么在sql中编写子查询获取分组内最新状态变更记录

子查询中用 `ROW_NUMBER()` 排序取每组最新记录

直接用 GROUP BY 拿不到“最新一条状态变更记录”，因为聚合函数（如 MAX(updated_at)）只能返回值，不能带回整行数据。正确做法是用窗口函数给每组内记录按时间倒序编号，再在外层筛选 rn = 1。

常见错误是写成：SELECT *, MAX(updated_at) FROM status_log GROUP BY order_id —— 这在大多数数据库（PostgreSQL、SQL Server、MySQL 8.0+）会报错，因为非聚合字段和 GROUP BY 不匹配；即使 MySQL 5.7 允许，结果也完全不可靠。

必须用 ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC)，不是 RANK() 或 DENSE_RANK()，避免并列时多取
排序字段建议加上二级条件，比如 ORDER BY updated_at DESC, id DESC，防止毫秒级时间重复导致顺序不确定
MySQL 5.7 及更早版本不支持窗口函数，得用关联子查询或自连接（见下节）

MySQL 5.7 兼容写法：用关联子查询找每组最大时间

当数据库不支持窗口函数时，核心思路是：先查出每个分组的最新时间，再用该时间反向关联原表取完整记录。但要注意“时间可能重复”这个坑——同一 order_id 下可能有两条记录都是 2024-05-20 10:00:00，直接 = 会取多条。

推荐写法：用 (order_id, updated_at) 联合子查询 + IN，但需确保 (order_id, updated_at) 是唯一组合，否则仍有歧义

更稳妥做法：加一层取 MAX(id)，假设 id 是自增主键且时间相同时 ID 更大者更新更晚：

SELECT t1.* FROM status_log t1
WHERE t1.id = (
  SELECT MAX(t2.id)
  FROM status_log t2
  WHERE t2.order_id = t1.order_id
);

性能关键：务必在 (order_id, updated_at) 或 (order_id, id) 上建联合索引，否则子查询会全表扫描

WHERE 条件里嵌套子查询过滤“最新状态为已发货”的订单

如果目标不是查所有最新记录，而是查“最新状态是已发货”的那些订单，别把过滤逻辑放在外层 WHERE status = 'shipped' —— 这会先取全部最新记录再过滤，效率低，且可能漏掉本应满足条件的订单（比如某订单最新记录确实是 shipped，但被其他条件误筛）。

正确做法：把状态判断放进子查询的 WHERE 或 HAVING，例如用窗口函数后加 AND status = 'shipped' 再 ROW_NUMBER()
或者更清晰：先用子查询拿到每组最新记录的 id，再用 IN 关联原表并加 status = 'shipped' 条件
注意 NULL：如果 status 字段允许为空，status = 'shipped' 会自动排除 NULL 行，无需额外处理

JOIN 场景下子查询别名必须显式声明

把最新状态记录作为子查询跟主表 JOIN 时，很多初学者忘记给子查询起别名，导致语法报错（如 PostgreSQL 报 subquery in FROM must have an alias）。

必须写成：SELECT o.*, s.status, s.updated_at FROM orders o JOIN (SELECT ... ) AS s ON o.id = s.order_id

MySQL 8.0+ 支持 CTE，可读性更好：

WITH latest_status AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM status_log
)
SELECT o.*, ls.status, ls.updated_at
FROM orders o
JOIN latest_status ls ON o.id = ls.order_id AND ls.rn = 1;