如何按issue_id分组获取每组when最大的status_changes完整记录

你需要获取每个issue_id对应的最新一条status_changes完整记录，以下是几种不同场景下的实现方案：

方案1：窗口函数（通用方案） #

支持所有兼容SQL:2003标准的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），使用ROW_NUMBER()窗口函数给同工单的变更记录按时间倒序排序，取排序第一的记录即可：

WITH ranked_changes AS (
  SELECT 
    *,
    -- 按工单ID分组，组内按变更时间倒序排序
    ROW_NUMBER() OVER (PARTITION BY issue_id ORDER BY "when" DESC) AS rn
  FROM status_changes
)
-- 取每个分组排序第一的最新记录
SELECT id, issue_id, status_from, status_to, "when"
FROM ranked_changes
WHERE rn = 1;

如果同一个工单存在多条完全同一时间戳的变更记录，需要全部返回的话，把ROW_NUMBER()替换为RANK()即可。

方案2：子查询关联（兼容低版本数据库） #

如果使用不支持窗口函数的低版本数据库（如MySQL 5.x），可以先分组查出每个工单的最新变更时间，再关联原表获取完整记录：

SELECT sc.*
FROM status_changes sc
INNER JOIN (
  -- 先查询每个工单的最新变更时间
  SELECT issue_id, MAX("when") AS latest_when
  FROM status_changes
  GROUP BY issue_id
) t ON sc.issue_id = t.issue_id AND sc."when" = t.latest_when;

方案3：PostgreSQL专属优化写法 #

如果使用PostgreSQL数据库，可以用DISTINCT ON语法实现更简洁、性能更好的查询：

SELECT DISTINCT ON (issue_id) *
FROM status_changes
ORDER BY issue_id, "when" DESC;

注意：when是SQL保留关键字，实际使用时请根据数据库类型添加对应的转义符：PostgreSQL用双引号"when"，MySQL用反引号`when`。

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