按ID与测试类型统计通过测试的总尝试次数的SQL实现求助

没问题，我来帮你搞定这个需求！首先得明确你的核心需求：对于每个用户（ID）和测试类型的组合，统计从第一次尝试到首次通过的总次数，并且把这个次数填充到该组合下的每一条记录里，对吧？

先看你的示例数据，刚好每个（ID, Test_Type）组都是最后一次尝试通过，所以总尝试次数等于该组的总记录数，但我们的SQL得考虑更通用的情况——比如万一某个组在首次通过后还有后续的测试记录，这时候应该只统计到首次通过的次数，而不是所有记录数。

给你两种可行的实现方案，你可以根据自己使用的SQL方言（比如MySQL、PostgreSQL、SQL Server等）选择合适的：

方案一：用窗口函数实现（推荐，更高效） #

这种方法只需要扫描一次数据表，性能更好：

WITH ranked_attempts AS (
    SELECT 
        *,
        -- 给每个(ID, Test_Type)组的尝试按日期排号，从1开始
        ROW_NUMBER() OVER (PARTITION BY ID, Test_Type ORDER BY Test_Date) AS attempt_num,
        -- 找到该组中首次通过的尝试次数，作为整个组的Attempts值
        MIN(CASE WHEN Test_Result = 'Pass' THEN attempt_num END) OVER (PARTITION BY ID, Test_Type) AS Attempts
    FROM your_test_table -- 这里替换成你的实际表名
)
SELECT 
    ID,
    Test_Date,
    Test_Type,
    Test_Result,
    Attempts
FROM ranked_attempts
ORDER BY ID, Test_Type, Test_Date;

解释：

• 第一步用ROW_NUMBER()窗口函数给每个用户的同一类型测试按日期排序，生成每次尝试的序号attempt_num；
• 第二步用MIN(...) OVER (...)在每个组里筛选出首次通过的那个序号（因为CASE会把非Pass的行转为NULL，MIN会忽略NULL，只取最小的Pass对应的序号），这个序号就是我们要的总尝试次数；
• 最后把这个值投影出来，每个组的所有行都会显示同一个Attempts值，完全符合你的需求。

方案二：用子查询关联实现（兼容性更好，适合不支持CTE的老版本SQL） #

如果你的SQL环境不支持公共表表达式（CTE），可以用子查询的方式：

SELECT 
    t.ID,
    t.Test_Date,
    t.Test_Type,
    t.Test_Result,
    fp.attempts_to_pass AS Attempts
FROM your_test_table t
JOIN (
    -- 先统计每个(ID, Test_Type)组首次通过的日期，再统计该日期前的总尝试次数
    SELECT 
        ID,
        Test_Type,
        COUNT(*) AS attempts_to_pass
    FROM your_test_table t1
    JOIN (
        -- 找到每个组首次通过的日期
        SELECT ID, Test_Type, MIN(Test_Date) AS first_pass_date
        FROM your_test_table
        WHERE Test_Result = 'Pass'
        GROUP BY ID, Test_Type
    ) t2 ON t1.ID = t2.ID AND t1.Test_Type = t2.Test_Type AND t1.Test_Date <= t2.first_pass_date
    GROUP BY ID, Test_Type
) fp ON t.ID = fp.ID AND t.Test_Type = fp.Test_Type
ORDER BY t.ID, t.Test_Type, t.Test_Date;

解释：

• 最内层子查询先找出每个（ID, Test_Type）组首次通过的日期；
• 中间层子查询关联原表，统计该组中所有日期早于等于首次通过日期的记录数，这个数就是总尝试次数；
• 最后把这个统计结果和原表关联，给每一行填充对应的Attempts值。

测试你的示例数据 #

把你的示例数据代入这两个SQL，都会得到你期望的输出：

需要注意的是，你要把SQL里的your_test_table替换成你实际使用的表名哦！如果你的测试日期有相同的情况，可能需要调整ORDER BY的字段（比如加上Test_Result或者其他唯一标识）来保证排序的唯一性。

内容来源于stack exchange