如何查询dbo.tblPresentationStatus表中状态流转异常的演示记录

我需要从dbo.tblPresentationStatus表中筛选出状态流转不符合规则的演示记录，具体规则如下：

状态流转规则 #

正确的流转流程：

Review → Approve → Presentation → Close

具体约束：

• 从REVIEW状态，同一PrID可变更为APPROVE、PRESENTATION或CLOSE
• 从APPROVE状态，同一PrID可变更为PRESENTATION或CLOSE
• 从PRESENTATION状态，同一PrID仅可变更为CLOSE

预期输出 #

根据测试数据，需返回以下存在错误流转的记录：

解决方案SQL #

-- 生成每个PrID的状态序列，并检查流转合法性
WITH StatusSequence AS (
    SELECT 
        PrID,
        PrStatus,
        StatusDate,
        LEAD(PrStatus) OVER (PARTITION BY PrID ORDER BY StatusDate) AS NextStatus,
        ROW_NUMBER() OVER (PARTITION BY PrID ORDER BY StatusDate) AS StatusOrder
    FROM #tblPresentationStatus
),
-- 筛选存在错误流转的PrID
InvalidPrIDs AS (
    SELECT DISTINCT PrID
    FROM StatusSequence
    WHERE 
        (PrStatus = 'REVIEW' AND NextStatus NOT IN ('APPROVE', 'PRESENTATION', 'CLOSE', NULL))
        OR (PrStatus = 'APPROVE' AND NextStatus NOT IN ('PRESENTATION', 'CLOSE', NULL))
        OR (PrStatus = 'PRESENTATION' AND NextStatus NOT IN ('CLOSE', NULL))
),
-- 将状态序列转成列展示
PivotedStatuses AS (
    SELECT 
        PrID,
        [1] AS Status1,
        [2] AS Status2,
        [3] AS Status3,
        [4] AS Status4
    FROM (
        SELECT PrID, PrStatus, StatusOrder
        FROM StatusSequence
        WHERE PrID IN (SELECT PrID FROM InvalidPrIDs)
    ) src
    PIVOT (
        MAX(PrStatus) FOR StatusOrder IN ([1], [2], [3], [4])
    ) pvt
)
SELECT * FROM PivotedStatuses

测试数据脚本 #

DROP TABLE IF EXISTS #tblPresentation

CREATE TABLE #tblPresentation
(
    PrID int NOT NULL,
    PrName nvarchar(100) NULL
)

INSERT INTO #tblPresentation 
VALUES (100, 'PrA'),
       (101, 'PrB'),
       (102, 'PrC'),
       (103, 'PrD')

DROP TABLE IF EXISTS #tblPresentationStatus

CREATE TABLE #tblPresentationStatus
(
    StatusID int NOT NULL,
    PrID int NOT NULL,
    PrStatus nvarchar(100) NOT NULL,
    StatusDate datetime NOT NULL
)

INSERT INTO #tblPresentationStatus 
VALUES
    -- 演示ID 100：符合规则的流转
    (1, 100, 'REVIEW', '2024-01-01 00:00:00.00'),
    (2, 100, 'APPROVE', '2024-01-02 00:00:00.00'),
    (3, 100, 'PRESENTATION', '2024-01-03 07:00:00.00'),
    (4, 100, 'CLOSE', '2024-01-03 10:00:00.00'),
    
    -- 演示ID 101：错误流转，从APPROVE转回REVIEW
    (5, 101, 'APPROVE', '2024-01-01 00:00:00.00'),
    (6, 101, 'REVIEW', '2024-01-03 10:00:00.00'),
 
    -- 演示ID 102：符合规则的流转（直接从REVIEW到PRESENTATION）
    (7, 102, 'REVIEW', '2024-01-01 00:00:00.00'),
    (8, 102, 'PRESENTATION', '2024-01-02 00:00:00.00'),
    (9, 102, 'CLOSE', '2024-01-03 10:00:00.00'),
 
    -- 演示ID 103：错误流转，从PRESENTATION转回APPROVE
    (10, 103, 'REVIEW', '2024-01-01 00:00:00.00'),
    (11, 103, 'PRESENTATION', '2024-01-02 00:00:00.00'),
    (12, 103, 'APPROVE', '2024-01-03 00:00:00.00'),
    (13, 103, 'CLOSE', '2024-01-04 00:00:00.00')

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