网页制造与网站建设论文,网站开发后端做那些,深圳人才一体化综合服务平台,租云服务器一个月多少钱有这样一个问题#xff0c;工厂中要统计某个供应商送货检验的情况#xff0c;依照其连续合格次数#xff0c;决定是否免检#xff0c;不使用游标或者循环#xff0c;如何写这个sql。 此情景也可以用于统计连胜记录等
先要学习一下 窗函数LAG#xff0c;指的是按分组和排…有这样一个问题工厂中要统计某个供应商送货检验的情况依照其连续合格次数决定是否免检不使用游标或者循环如何写这个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 ;
