SQL如何按TestCaseId分组取最新5条运行记录统计非成功结果总和

解决方案 #

核心逻辑 #

使用窗口函数为每个TestCaseId下的运行记录按执行先后排序，筛选出每个用例最近5条运行记录后再做聚合统计，一次查询即可拿到所有目标用例的结果，无需多次调用。

适配MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库 #

WITH ranked_records AS (
    SELECT 
        TestCaseId,
        TestSuiteId,
        TestSuiteCollectionId,
        StatusId,
        -- 按TestCaseId分组，id倒序（id越大代表运行时间越近）编号
        ROW_NUMBER() OVER (PARTITION BY TestCaseId ORDER BY id DESC) AS run_rank
    FROM testreporting.testresults
    WHERE
        TestSuiteCollectionId = 461
        -- 原IN列表存在拼写问题：443后面漏了单引号和逗号，还有重复的3846，已修正格式
        AND TestCaseId IN ('3846', '3840', '3841', '3842', '3845', '5701', '4015', '4008', '3844', '4012', '5702', '3843', '4014', '3847', '4635', '4295', '4446', '4637', '4296', '4297', '443')
)
SELECT 
    TestCaseId,
    TestSuiteId,
    TestSuiteCollectionId,
    SUM(CASE WHEN StatusId != 1 THEN 1 ELSE 0 END) AS failed_count
FROM ranked_records
WHERE run_rank <= 5 -- 仅取每个用例最近5次运行记录
GROUP BY TestCaseId, TestSuiteId, TestSuiteCollectionId
-- 可按需添加HAVING过滤异常用例，比如仅返回存在失败的用例：
-- HAVING failed_count > 0
ORDER BY TestCaseId;

补充说明 #

1. 你之前写的单TestCaseId查询逻辑存在错误：LIMIT 5写在GROUP BY之后，实际是限制聚合后的结果输出行数，不是取聚合前的最近5条原始记录统计，结果不符合预期。
2. 如果使用的是不支持窗口函数的MySQL 5.x及更低版本，可以用关联子查询实现，性能略低于窗口函数方案：

SELECT 
    t1.TestCaseId,
    t1.TestSuiteId,
    t1.TestSuiteCollectionId,
    SUM(CASE WHEN t1.StatusId != 1 THEN 1 ELSE 0 END) AS failed_count
FROM testreporting.testresults t1
WHERE
    t1.TestSuiteCollectionId = 461
    AND t1.TestCaseId IN ('3846', '3840', '3841', '3842', '3845', '5701', '4015', '4008', '3844', '4012', '5702', '3843', '4014', '3847', '4635', '4295', '4446', '4637', '4296', '4297', '443')
    AND (
        SELECT COUNT(*) 
        FROM testreporting.testresults t2 
        WHERE t2.TestCaseId = t1.TestCaseId AND t2.id > t1.id
    ) < 5 -- 比当前记录id大的同用例记录小于5条，即当前记录属于最近5条
GROUP BY t1.TestCaseId, t1.TestSuiteId, t1.TestSuiteCollectionId
ORDER BY t1.TestCaseId;

内容的提问来源于stack exchange，提问作者Mads Sander Høstrup