22,300
社区成员




where RS is null
;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from
(select ID,
case RS
WHEN 0.0 THEN NULL
WHEN 999.9 THEN NULL
ELSE RS
end AS RS
from @tb) a
where RS is not null
),cte2 as(
select
COUNT(*) over(partition by row) as [连续总计],
ID,RS
from cte
)
select *
from cte2
where [连续总计]=(select MAX([连续总计]) from cte2)
declare @tb table
(
ID INT IDENTITY(1,1),
RS NUMERIC(10,1)
)
INSERT INTO @tb
select 0 union all
select 13.9 union all
select 49.1 union all
select 6.4 union all
select 3.4 union all
select 0.3 union all
select 999.9 union all
select 2.9 union all
select 1.2 union all
select 0 union all
select 0 union all
select 25.9 union all
select 0.5 union all
select 0 union all
select 0 union all
select 0 union all
select 0 union all
select 0.3 union all
select 9.6 union all
select 7.2 union all
select 13.5 union all
select 999.9
;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from
(select ID,
case RS
WHEN 0.0 THEN NULL
WHEN 999.9 THEN NULL
ELSE RS
end AS RS
from @tb) a
where RS is not null
)
select
COUNT(*) over(partition by row) as [连续总计],
ID,RS
from cte
连续总计 ID RS
----------- ----------- ---------------------------------------
5 2 13.9
5 3 49.1
5 4 6.4
5 5 3.4
5 6 0.3
2 8 2.9
2 9 1.2
2 12 25.9
2 13 0.5
4 18 0.3
4 19 9.6
4 20 7.2
4 21 13.5