SQL按Component分组取Version最高对应完整行记录如何实现

错误原因说明 #

你之前用GROUP BY + MAX(Version)的写法出错是因为：当GROUP BY分组后，SELECT子句里出现既不属于分组键、也不属于聚合函数的字段时，数据库不会主动匹配MAX(Version)对应的行记录，而是随机返回该分组下的任意一行的字段值，所以才会出现Version取值正确，但ID、Name等其他字段不匹配的问题。

正确实现方案 #

方案1：关联子查询法（兼容绝大多数数据库，包括MySQL 5.x、8.0、PostgreSQL等）

先通过子查询查出每个Component对应的最高有效Version，再关联回原表匹配到最高版本对应的完整记录：

SELECT m.ID, m.Name, m.Component, m.Version
FROM modules m
INNER JOIN (
    -- 先查每个Component的最高有效版本
    SELECT Component, MAX(Version) AS MaxVersion
    FROM modules
    WHERE ValidTo > NOW() AND ValidFrom < NOW()
    GROUP BY Component
) max_version ON m.Component = max_version.Component AND m.Version = max_version.MaxVersion
-- 外层再次过滤有效期，缩小匹配范围提升性能
WHERE m.ValidTo > NOW() AND m.ValidFrom < NOW();

方案2：窗口函数法（支持窗口函数的数据库更推荐，比如MySQL 8.0+、PostgreSQL、SQL Server等）

用ROW_NUMBER()窗口函数按Component分组后按Version倒序排序，取每个分组排序第一的记录即可：

SELECT ID, Name, Component, Version
FROM (
    SELECT 
        ID, Name, Component, Version,
        -- 按Component分组，Version倒序排序，同组内最高版本的行rn=1
        ROW_NUMBER() OVER (PARTITION BY Component ORDER BY Version DESC) AS rn
    FROM modules
    WHERE ValidTo > NOW() AND ValidFrom < NOW()
) t
WHERE rn = 1;

如果存在同一个Component有多个相同最高Version的记录需要全部返回，把ROW_NUMBER()替换为RANK()即可。

注意事项 #

如果Version字段是字符串格式，遇到版本号位数不一致的情况（比如1.10.0和1.2.0），直接按字符串排序会判定1.2.0更大，导致排序错误。MySQL环境下可以把排序逻辑修改为ORDER BY INET_ATON(REPLACE(Version, '.', '0')) 或者自定义版本号处理函数，保证排序结果符合预期。

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