22,300
社区成员




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)