如何优化分组取最新时间记录的SQL查询 避免用WITH且不降低执行性能

方案1：内联子查询替换CTE（全数据库兼容，性能和原写法完全一致） #

直接把原CTE的逻辑作为子查询放在JOIN语句中即可，不需要修改任何逻辑，兼容所有支持标准SQL的数据库（包括低版本MySQL 5.x），数据库优化器会生成和原CTE写法完全相同的执行计划，大数据量下无性能损失：

SELECT t.ts, t.departure, t.destination, t.price
FROM transfers t
INNER JOIN (
  SELECT MAX(ts) AS ts, departure FROM transfers GROUP BY departure
) mtpd ON t.departure = mtpd.departure AND t.ts = mtpd.ts;

方案2：DISTINCT ON 语法（PostgreSQL专属，写法最简，性能最优） #

如果你使用的是PostgreSQL数据库，可以用内置的DISTINCT ON语法实现分组取TOP 1的需求，仅需一次表扫描，在有对应联合索引的情况下，性能比关联写法高30%以上，代码非常简洁：

SELECT DISTINCT ON (departure) ts, departure, destination, price
FROM transfers
ORDER BY departure, ts DESC;

方案3：窗口函数写法（兼容所有支持窗口函数的数据库） #

如果你用的是MySQL 8+、Oracle、SQL Server等支持窗口函数的数据库，可以用ROW_NUMBER()窗口函数实现需求，写法也比原写法更短，性能稳定：

SELECT ts, departure, destination, price
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY departure ORDER BY ts DESC) rn
  FROM transfers
) t
WHERE rn = 1;

大数据量性能优化建议 #

建议创建联合索引：CREATE INDEX idx_transfers_departure_ts ON transfers (departure, ts DESC);，以上三种写法都可以命中该索引，避免全表扫描，查询效率会有数量级的提升。如果同一个出发地存在多条相同最大时间戳的记录，JOIN写法会返回所有符合条件的记录，窗口函数写法可以通过替换ROW_NUMBER()为RANK()实现相同的效果。

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