不就是简单的套一层么。

with t0 as(

    select '202201' as id,1 as num

    union all

    select '202202' as id,1 as num

    union all

    select '202203' as id,1 as num

    union all

    select '202204' as id,1 as num

    union all

    select '202205' as id,0 as num

    union all

    select '202206' as id,1 as num

    union all

    select '202207' as id,0 as num

    union all

    select '202208' as id,1 as num

    union all

    select '202209' as id,1 as num

    union all

    select '202301' as id,1 as num

    union all

    select '202302' as id,1 as num

    union all

    select '202303' as id,0 as num

    union all

    select '202304' as id,0 as num

    union all

    select '202305' as id,1 as num

    union all

    select '202306' as id,1 as num

)

,t1 as(

    select

        id,

        row_number() over(order by id) as id2,

        num

    from t0

)

select

    id,

    id2,

    num,

    rn,

    type,

    case 

        when rn is null then 0

        else row_number() OVER (partition by type order by id)

    end AS new_num

from(

    select     

        id,    

        id2,

        num,

        rn,    

        id2 - rn as type

    from(

        SELECT

            x.id,

            x.id2,

            rn,

            num

        FROM

            t1 x

        left join(

            select id2,row_number() over(order by id2) as rn from t1 where num = 1

        ) y

        on x.id2 = y.id2

    ) x

) y

order by id

存储过程-游标

SELECT id,  

       SUM(num) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS num  

FROM (  

  SELECT id,  

         CASE   

           WHEN num = 0 THEN 0   

           WHEN num = 1 THEN 1   

         END AS num  

  FROM your_table  

) t

换一个

SELECT id,  

       SUM(CASE WHEN num = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS cumulative_sum  

FROM your_table;

在换一个

SELECT id,  

       CASE   

         WHEN num = 1 THEN SUM(num) OVER (ORDER BY id)  

         ELSE 0  

       END AS num  

FROM your_table;

SqlServer？oracle？mysql？