求助求助如何查询数据库里某一数值连续出现的最大次数

DECLARE @tb TABLE

(

    da datetime,

    RS numeric(10,1)

)



INSERT INTO @tb

SELECT '1960-03-04', 0.0 UNION ALL

SELECT '1960-03-05', 13.9 UNION ALL

SELECT '1960-03-06', 49.1 UNION ALL

SELECT '1960-03-07', 6.4 UNION ALL

SELECT '1960-03-08', 3.4 UNION ALL

SELECT '1960-03-09', 0.3 UNION ALL

SELECT '1960-03-10', 999.9 UNION ALL

SELECT '1960-03-11', 2.9 UNION ALL

SELECT '1960-03-12', 1.2 UNION ALL

SELECT '1960-03-13', 0.0 UNION ALL

SELECT '1960-03-14', 0.0 UNION ALL

SELECT '1960-03-15', 25.9 UNION ALL

SELECT '1960-03-16', 0.5 UNION ALL

SELECT '1960-03-17', 0.0 UNION ALL

SELECT '1960-03-18', 0.0 UNION ALL

SELECT '1960-03-19', 0.0 UNION ALL

SELECT '1960-03-20', 0.0 UNION ALL

SELECT '1960-03-21', 0.3 UNION ALL

SELECT '1960-03-22', 9.6 UNION ALL

SELECT '1960-03-23', 7.2 UNION ALL

SELECT '1960-03-24', 13.5 UNION ALL

SELECT '1960-03-25', 999.9



;WITH a AS (

    SELECT da,

           ROW_NUMBER() OVER(order by da) AS rn

      FROM @tb

)

,b AS (

    SELECT da,

           ROW_NUMBER() OVER(order by da) AS rn

      FROM @tb

     WHERE rs <> 0.0

       AND rs <> 999.9

)

,c AS (

    SELECT a.da,

           a.rn - b.rn AS g

      FROM a

      JOIN b

        ON a.da = b.da

)

,d AS (

    SELECT g,

           COUNT(*) 连续天数,

           MIN(da) 开始日期,

           MAX(da) 结束日期

      FROM c

  GROUP BY g

)

--SELECT * FROM d

SELECT MAX(连续天数) 最长连续天数 FROM d

    g    连续天数 开始日期   结束日期

----- ----------- ---------- ----------

    1           5 1960-03-05 1960-03-09

    2           2 1960-03-11 1960-03-12

    4           2 1960-03-15 1960-03-16

    8           4 1960-03-21 1960-03-24

最长连续天数

------------

           5

declare @tb table
(
    da datetime,
    RS NUMERIC(10,1)
)

INSERT INTO @tb(rs)
select 0 union all
select 0union all
select 0 union all
select 999.9 union all
select 999.9 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 ROW_NUMBER() over(order by getdate()) AS num,
       case 
        WHEN RS=0 OR RS=999.9  THEN RS
        ELSE NULL
    end  AS RS
from @tb
),cte1 AS (
	SELECT num-ROW_NUMBER() over(order by getdate()) AS nums,
			*
	FROM cte
	WHERE RS IS NOT NULL
),cte2 AS(
SELECT COUNT(nums) OVER(partition BY nums) AS counts,*
FROM cte1
)

SELECT MAX(counts)AS 最大次数 FROM cte2

with cte as(

select da-ROW_NUMBER() over(order by getdate()) as row,da,RS

from 

(select da,

case RS

WHEN 0.0 THEN NULL

WHEN 999.9 THEN NULL

ELSE RS

end  AS RS

from chuxiong) a

where RS is null and  da between '1960-03-04' and '1964-05-04'

),cte2 as(

select

COUNT(*) over(partition by row) as [连续总计],

CONVERT(CHAR(10),da,121) as [日期],RS as 降雨

from cte)

select * 

from cte2

where [连续总计]=(select MAX([连续总计]) from cte2)