SQL 最大连续合格次数 最大连胜记录次数 最大连败记录次数

有这样一个问题，工厂中要统计某个供应商送货检验的情况，依照其连续合格次数，决定是否免检，不使用游标或者循环，如何写这个sql。
此情景也可以用于统计连胜记录等

先要学习一下 窗函数LAG，指的是按分组和排序，取到之前(before)行的值。

假如表是这样的：

建表语句如下：

CREATE TABLE InspectionResults (
  ID int NOT NULL AUTO_INCREMENT,
  MaterialCode varchar(50) DEFAULT NULL,
  InspectionTime datetime DEFAULT NULL,
  InspectionOutcome varchar(10) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 1,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

按照物料，统计最大的连续合格次数，结果是：

以下是sql语句

WITH RankedResults AS (  
    SELECT  
        MaterialCode,  
        InspectionTime,  
        InspectionOutcome,  
        CASE  
            WHEN InspectionOutcome = 'Y' AND   
                 (LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) IS NULL OR   
                  LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) <> 'Y')  
            THEN 1 
            ELSE 0  
        END AS StartSequence  
    FROM  
        InspectionResults  
),  
ConsecutiveGroups AS (  
    SELECT  
        MaterialCode,  
        InspectionTime,  
        InspectionOutcome,  
        SUM(StartSequence) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) AS SequenceGroup  
    FROM  
        RankedResults  
    WHERE  
        InspectionOutcome = 'Y'  
),  
MaxConsecutiveCounts AS (  
    SELECT  
        MaterialCode,  
        SequenceGroup,  
        COUNT(*) AS ConsecutiveCount  
    FROM  
        ConsecutiveGroups  
    GROUP BY  
        MaterialCode,  
        SequenceGroup  
)  
SELECT  
    MaterialCode,  
    MAX(ConsecutiveCount) AS MaxConsecutivePasses  
FROM  
    MaxConsecutiveCounts  
GROUP BY  
    MaterialCode;

关键的中间步骤，请注意观察表中的数据：

WITH RankedResults AS (  
    SELECT  
        MaterialCode,  
        InspectionTime,  
        InspectionOutcome,  
        CASE  
            WHEN InspectionOutcome = 'Y' AND   
                 (LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) IS NULL OR   
                  LAG(InspectionOutcome) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) <> 'Y')  
            THEN 1   
            ELSE 0  
        END AS StartSequence  
    FROM  
        InspectionResults  
) 
SELECT  
        MaterialCode,  
        InspectionTime,  
        InspectionOutcome, 
        StartSequence, 
        SUM(StartSequence) OVER (PARTITION BY MaterialCode ORDER BY InspectionTime) AS SequenceGroup  
FROM  
        RankedResults  ;