Sql 在数据分区内执行确定

解释逻辑的注释嵌入在查询中.

with cte as (
    select id, nbr, dt, status
        -- get a row number so we can only show the calculated data once per partition
        , row_number() over (partition by id, nbr order by id, nbr, dt asc, status desc) rn
        -- Determine whether status 1 exists within the partition
        , max(status) over (partition by id, nbr) partition_status
        -- Get the first date where Status = 1
        , min(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt1
        -- Get the last date where Status = 1
        -- Only required if there is the possibility of more than one Status = 1 row in a partition
        , max(case when Status = 1 then dt else null end) over (partition by id, nbr) partition_dt2
    from @t
)
select id, nbr, dt, status
    -- On the first row of each partition show whether a status = 1 exists
    , case when rn = 1 then partition_status else 0 end Cont1Sta1
    -- On the first row of each partition show the date lag in days
    , case when rn = 1 then datediff(day, dt, coalesce(partition_dt1, dt)) else 0 end DateLagInDays
    -- On the first row of each partition show whether any records exist after the last Status = 1
    , case when rn = 1 and exists (select 1 from cte c2 where c2.id = c1.id and c2.nbr = c1.nbr and c2.dt > c1.partition_dt2) then 1 else 0 end Recurrence
from cte c1
order by id, nbr, dt asc, status desc;

按要求返回:

DBFiddle