递归CTE还是ROW_NUMBER分区？SQL按状态重置计数实现问题

我懂你遇到的问题了——你需要按CDO_OP_REFERRAL_UNIQUE_ID分组，每当ATTENDED_OR_DID_NOT_ATTEND的取值发生变化时，就把计数重置为1；如果是连续相同的取值，就让计数递增。这属于SQL里经典的岛屿与缺口问题，单纯用ROW_NUMBER() OVER(PARTITION BY ...)确实搞不定，因为它没法识别连续的相同值“岛屿”。

解决方案思路 #

我们需要先给每一组连续相同的ATTENDED_OR_DID_NOT_ATTEND值分配一个唯一的分组ID，然后在每个分组内使用ROW_NUMBER()来生成递增计数：

1. 使用LAG()函数获取当前行的上一行ATTENDED_OR_DID_NOT_ATTEND值，和当前行对比，若不同则标记为1（表示新分组开始），否则标记为0。
2. 用SUM() OVER()累加这些标记值，得到每个连续相同值组的分组ID。
3. 最后在CDO_OP_REFERRAL_UNIQUE_ID和分组ID的分区内，用ROW_NUMBER()生成你需要的计数结果。

完整SQL代码 #

DECLARE @CDO_OP_APPOINTMENT TABLE (
    CDO_OP_REFERRAL_UNIQUE_ID int,
    LOCAL_PATIENT_NUMBER varchar(10),
    APPOINTMENT_START_DATE_TIME datetime,
    ATTENDED_OR_DID_NOT_ATTEND varchar(10),
    DESIRED_OUTCOME varchar(10)
) 

INSERT INTO @CDO_OP_APPOINTMENT VALUES 
('480805568', 'HEY1030785', '05/11/2013 10:00', '2', '1'), 
('480805568', 'HEY1030785', '12/11/2013 10:00', '5', '1'), 
('480805568', 'HEY1030785', '22/11/2013 09:30', '5', '2'), 
('480805568', 'HEY1030785', '03/12/2013 13:00', '3', '1'), 
('480805568', 'HEY1030785', '30/12/2013 10:15', '5', '1'), 
('480805568', 'HEY1030785', '24/02/2014 09:15', '4', '1'), 
('480805568', 'HEY1030785', '24/02/2014 14:15', '5', '1'), 
('480805568', 'HEY1030785', '17/03/2014 15:25', '4', '1'), 
('480805568', 'HEY1030785', '20/03/2014 18:50', '5', '1'), 
('480805568', 'HEY1030785', '23/09/2014 15:55', '5', '2'), 
('480805568', 'HEY1030785', '14/04/2015 16:30', '5', '3'), 
('480805568', 'HEY1030785', '14/04/2015 17:30', '4', '1'), 
('480805568', 'HEY1030785', '15/05/2015 14:15', '5', '1')

-- 核心查询部分
SELECT 
    CDO_OP_REFERRAL_UNIQUE_ID,
    LOCAL_PATIENT_NUMBER,
    APPOINTMENT_START_DATE_TIME,
    ATTENDED_OR_DID_NOT_ATTEND,
    DESIRED_OUTCOME,
    -- 生成符合要求的计数
    ROW_NUMBER() OVER(
        PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID, GroupId 
        ORDER BY APPOINTMENT_START_DATE_TIME
    ) AS CALCULATED_OUTCOME
FROM (
    SELECT 
        *,
        -- 生成连续相同值的分组ID
        SUM(GroupStartFlag) OVER(
            PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID 
            ORDER BY APPOINTMENT_START_DATE_TIME
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM (
        SELECT 
            *,
            -- 判断当前行是否是新分组的开始
            CASE WHEN LAG(ATTENDED_OR_DID_NOT_ATTEND) OVER(
                PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID 
                ORDER BY APPOINTMENT_START_DATE_TIME
            ) != ATTENDED_OR_DID_NOT_ATTEND THEN 1 ELSE 0 END AS GroupStartFlag
        FROM @CDO_OP_APPOINTMENT
    ) AS FlaggedRows
) AS GroupedRows
ORDER BY APPOINTMENT_START_DATE_TIME

结果验证 #

运行以上代码后，CALCULATED_OUTCOME列的值会和你提供的DESIRED_OUTCOME完全一致，完美实现了“每当ATTENDED_OR_DID_NOT_ATTEND变化时重置计数”的需求。

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