有这样一个问题,工厂中要统计某个供应商送货检验的情况,依照其连续合格次数,决定是否免检,不使用游标或者循环,如何写这个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语句文章来源:https://www.toymoban.com/news/detail-804793.html
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;
关键的中间步骤,请注意观察表中的数据:
文章来源地址https://www.toymoban.com/news/detail-804793.html
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 ;
到了这里,关于SQL 最大连续合格次数 最大连胜记录次数 最大连败记录次数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!