递归CTE中窗口函数count结果异常，求修正方案实现预期统计

我编写了一个递归CTE，在其递归部分使用count(s_) over (partition by c) cc计算cc列，但得到的结果均为1，无法达到预期的统计效果（如预期结果中c1组的cc值为2）。我的需求是在每次迭代（按n列）中完成以下操作：

• 按c分区统计s_的数量，得到cc
• 计算cc的最小值
• 排除对应最小值的s值后进入下一轮迭代，重复执行上述三步，且需要正确的cc值用于后续逻辑

测试代码 #

create table cor (nb int, s varchar(10), c varchar(10))

insert into cor values ( 1 , 's1' , 'c1'  )
insert into cor values ( 1, 's2' , 'c1')
insert into cor values ( 1, 's3' , 'c2' ) 
insert into cor values ( 1, 's4' , 'c3' )
insert into cor values ( 1, 's5' , 'c3' )
insert into cor values ( 1, 's6' , 'c4')

create table sr (num int, s varchar(10))


insert into sr values ( 1  , 's1' )
insert into sr values ( 1 , 's2' )
insert into sr values ( 1 , 's3' )
insert into sr values ( 2  , 's1' )
insert into sr values ( 2 , 's2' )
insert into sr values ( 2  , 's6' )
insert into sr values ( 3 , 's1' )
insert into sr values ( 3 , 's3' )
insert into sr values ( 3 , 's4' )
insert into sr values ( 4 , 's1' )
insert into sr values ( 4  , 's3' )
insert into sr values ( 4  , 's4' )
insert into sr values ( 4 , 's6')
with  rec as (


select  1 n, nb, s , c , cast(0 as int) num, cast('' as varchar(50)) s_  , 0 nn, 0 cc  from cor 

union all
select r.*, count (s_) over (partition by c) cc
from 
(
    select t.*, cast(row_number()over(partition by n,s, c order by t.s_ desc ) as int)nn
    from(
        select   1+n n,   cast(sr.num as int) num_, rec.s, rec.c, cast(sr.num as int) num,iif(cast(sr.s as varchar(50))<>rec.s,'1',cast(sr.s as varchar(50)))  s_
                
                    from rec, sr where sr.num=rec.n+1  and sr.num<5
        )t
)r where nn=1
)

select * from rec 
order by n,s

当前执行结果 #

预期结果 #

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