如何使用T-SQL统计各车辆的连续检测失败次数

该需求属于按维度统计连续序列计数的场景，根据你给出的预期输出，是按车辆品牌单独统计各自的连续失败次数，可以通过窗口函数配合分组逻辑实现，具体查询语句如下（假设你的表名为CarInspections）：

WITH GroupedRecords AS (
    SELECT 
        [Date],
        Car,
        Result,
        -- 按车辆分组，每遇到一次PASS分组编号+1，同一品牌的连续FAIL会被分到同一组
        SUM(CASE WHEN Result = 'PASS' THEN 1 ELSE 0 END) OVER (PARTITION BY Car ORDER BY [Date] ASC) AS FailGroup
    FROM CarInspections
)
SELECT 
    [Date],
    Car,
    CASE 
        WHEN Result = 'PASS' THEN 0
        -- 同一车辆同一分组内按时间排序的行号就是对应的连续失败次数
        ELSE ROW_NUMBER() OVER (PARTITION BY Car, FailGroup ORDER BY [Date] ASC)
    END AS Failures
FROM GroupedRecords
ORDER BY [Date] ASC;

逻辑说明 #

1. CTE中使用累计求和窗口函数，按车辆维度分组后累计计算PASS出现的次数，作为分组标识：每出现一次PASS，分组编号就加1，这样同一品牌两次PASS之间的所有FAIL记录都会被划分到同一个分组中
2. 外层查询对记录做判断：如果检测结果为PASS直接返回0，结果为FAIL的话，计算该记录在当前车辆当前分组内按时间排序的行号，就是对应的连续失败次数
   如果你需要全局统计不区分车辆，去掉两个窗口函数中的PARTITION BY Car即可。

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