SQL group by后取最高版本对应非空test_result值的实现方法

你的需求是为每个测试用例取最高版本的非空测试结果，这类需求完全可以通过SQL直接实现，不需要转移到后端代码处理，数据库层执行此类分组筛选的效率远高于拉取数据到后端遍历。

现有代码问题 #

你当前的SQL存在错误：GROUP BY test_id会让每个测试用例仅保留一行，窗口函数ROW_NUMBER()无法拿到所有版本的行完成排序，逻辑不成立。

正确实现方式 #

方案1：窗口函数实现（兼容性最好，支持所有支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH ranked_valid_results AS (
    SELECT
        test_id,
        test_result,
        -- 同一测试用例下，非空结果按版本倒序排序
        ROW_NUMBER() OVER (PARTITION BY test_id ORDER BY version DESC) AS row_num
    FROM TEST_RESULT
    WHERE test_result IS NOT NULL -- 提前过滤无结果的行，减少计算量
)
SELECT 
    test_id,
    test_result AS final_result
FROM ranked_valid_results
WHERE row_num = 1; -- 取排序第一的就是最高版本的有效结果

方案2：关联子查询实现（兼容不支持窗口函数的低版本数据库） #

SELECT 
    t1.test_id,
    t1.test_result AS final_result
FROM TEST_RESULT t1
INNER JOIN (
    -- 先找到每个测试用例的最高有效版本
    SELECT 
        test_id,
        MAX(version) AS max_valid_version
    FROM TEST_RESULT
    WHERE test_result IS NOT NULL
    GROUP BY test_id
) t2 ON t1.test_id = t2.test_id AND t1.version = t2.max_valid_version;

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

如果使用PostgreSQL，可使用DISTINCT ON语法更简洁的实现：

SELECT DISTINCT ON (test_id)
    test_id,
    test_result AS final_result
FROM TEST_RESULT
WHERE test_result IS NOT NULL
ORDER BY test_id, version DESC;

特殊场景说明 #

如果你的版本号命名规则不是简单的v+个位数（比如出现v10这类大于v9的版本），需要额外处理版本号排序逻辑，把版本号里的数字提取出来转为数值类型排序，避免出现字符串排序下v10 < v2的问题。

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