替代GROUP BY获取文档及对应最新版本的SQL解决方案

背景 #

我正在开发基于Laravel 8和MySQL 8.0.27的自定义文档管理系统（DMS），包含两张核心表：

• documents表：存储文档基础信息，包括id、保密级别、文档类型等
• versions表：存储版本相关信息，包括标题、文件类型、原始文件名、各类分类编码等
  两张表为一对多关系，即一个文档对应一个或多个版本。

前端使用datatables.net，需要获取包含documents表信息及对应文档仅最新版本数据的结果集。

最初实现的SQL #

SELECT d.id, d.confidentiality, v.id as version_id, 
    v.title, v.system_code, v.system_code,
    v.version, v.updated_at, v.created_at, v.filename_orig 
FROM documents d
INNER JOIN 
(SELECT * FROM versions ORDER BY versions.created_at DESC) v
ON d.id = v.document_id
GROUP BY d.id

问题原因 #

这段SQL在phpMyAdmin中能正常运行，但在应用中报错，核心原因是与sql_mode=only_full_group_by不兼容：该模式要求MySQL遵循通用数据库规范，SELECT语句中的所有非聚合字段必须出现在GROUP BY子句中。若按此要求修改SQL，会返回文档的所有版本数据，无法得到每个文档唯一的结果。

关闭only_full_group_by配置虽能临时解决问题，但不符合规范，不推荐使用。

优化后的解决方案 #

参考相关思路后，以下SQL可正常运行，且完全符合only_full_group_by模式的要求：

WITH latest AS (
  SELECT v.*, ROW_NUMBER() OVER (PARTITION BY v.document_id ORDER BY v.created_at DESC) AS rn
  FROM versions v
)
SELECT * FROM latest l 
JOIN documents d ON l.document_id = d.id
WHERE l.rn = 1

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