请教查询各月连续无降雨最大天数的SQL语句如何写？
请教查询各月连续无降雨最大天数的SQL语句如何写？
表a数据结构如下：
---------------------------------
年 月 日 降雨量
2013 1 1 7.3
2013 1 2 2.8
2013 1 3
2013 1 4
2013 1 5
2013 1 6
2013 1 7
2013 1 8 1.5
2013 1 9
2013 1 10
2013 1 11 3.2
....
2013 1 31 2.1
2013 2 1 3.2
2013 2 2 4.0
....
---------------------------------
表a的资料是逐月逐日并且连续的资料，现在我需要查询的是每个月中连续几天无降雨（即空值）的最大天数。比如说2013年1月3日～7日已经连续5天无降雨，且为2013年1月中最大的连续无降雨天数，而其他日期仅间隔1～4天无降雨，那么我需要查询出来的结果就是：
---------------------------------
年 月 连续无降雨的最大天数
2013 1 5
--------------------------------
请问各位老大，这样的查询语句怎么写？
[解决办法]
with tb(a,b,c,d)as(
select 2013,1,1,7.3 union
select 2013,1,2,2.8 union
select 2013,1,3,null union
select 2013,1,4,null union
select 2013,1,5,null union
select 2013,1,6,null union
select 2013,1,7,null union
select 2013,1,8,1.5 union
select 2013,1,9,null union
select 2013,1,10,null union
select 2013,1,11,3.2),
tc as(
select *,row_number() over(order by c) num from tb
where d is null)
select top 1 a,b,count(c-num) from tc
group by a,b,c-num
order by count(c-num) desc
SELECT 2013, 1, 3, null UNION all
SELECT 2013, 1, 4, null UNION all
SELECT 2013, 1, 5, null UNION all
SELECT 2013, 1, 6, null UNION all
SELECT 2013, 1, 7, null UNION all
SELECT 2013, 1, 8, 1.5 UNION all
SELECT 2013, 1, 9,null UNION all
SELECT 2013, 1, 10, null UNION all
SELECT 2013, 1, 11, null
)
,a2 AS
(
SELECT yy,mm,dd,CASE WHEN rain is NULL THEN 0 else 1 end rain,ROW_NUMBER() OVER(ORDER BY yy,mm,dd) re FROM a1
)
,a3 AS
(
SELECT *,(SELECT rain FROM a2 WHERE re=a.re+1) rain2 FROM a2 a
)
,a4 AS
(
SELECT * FROM a3 WHERE rain2 IS NULL OR rain2<>rain
)
,a5 AS
(
SELECT *,
ISNULL(
(SELECT TOP 1 dd FROM a4 WHERE yy=a.yy AND mm=a.mm AND re>=a.re ORDER BY re asc),
DATEDIFF(dd,RTRIM(yy)+'-'+RTRIM(mm)+'-1',DATEADD(mm,1,RTRIM(yy)+'-'+RTRIM(mm)+'-1'))
) dd_end
FROM a2 a
)
,b3 AS
(
SELECT *,(SELECT rain FROM a2 WHERE re=a.re-1) rain2 FROM a2 a
)
,b4 AS
(
SELECT * FROM b3 WHERE rain2 IS NULL OR rain2<>rain
)
,b5 AS
(
SELECT *,
ISNULL(
(SELECT TOP 1 dd FROM b4 WHERE yy=a.yy AND mm=a.mm AND re<=a.re ORDER BY re desc)
,1
) dd_start
FROM a2 a
)
,c1 AS
(
SELECT a.*,b.dd_start
FROM a5 a
INNER JOIN b5 b ON a.re=b.re
WHERE a.rain=0
)
,c2 AS
(
SELECT yy,mm,dd_start,dd_end
FROM c1
GROUP BY yy,mm,dd_start,dd_end
)
SELECT yy,mm,MAX(dd_end-dd_start+1)
FROM c2
GROUP BY yy,mm
[解决办法]
CREATE TABLE tRain(yy INT ,mm INT ,dd INT ,Qty DECIMAL(18,1))
INSERT INTO tRain
VALUES
(2013, 1, 1, 7.3)
,(2013, 1 , 2 , 2.8)
,(2013, 1, 3 , NULL)
,(2013, 1, 4 , NULL)
,(2013, 1 , 5 , NULL)
,(2013, 1, 6 , NULL)
,(2013, 1, 7 , NULL)
,(2013, 1 , 8 , 1.5)
,(2013, 1 , 9,NULL)
,(2013, 1 , 10 , NULL)
,(2013, 1, 11 , 3.2)
WITH CTE AS (
SELECT *,rn=ROW_NUMBER() OVER(ORDER BY dd) FROM tRain
WHERE ISNULL(Qty,0)=0
)
select top 1 yy 年,mm 月,count(dd-rn) 连续无降雨的最大天数 from CTE
group by yy,mm,dd-rn
order by count(dd-rn) desc
/*结果
年月连续无降雨的最大天数
201315
*/
[解决办法]
create table 表a
(年 varchar(4), 月 varchar(2), 日 varchar(2), 降雨量 varchar(5))
insert into 表a
select '2013', '1', '1', '7.3' union all
select '2013', '1', '2', '2.8' union all
select '2013', '1', '3', '' union all
select '2013', '1', '4', '' union all
select '2013', '1', '5', '' union all
select '2013', '1', '6', '' union all
select '2013', '1', '7', '' union all
select '2013', '1', '8', '1.5' union all
select '2013', '1', '9', '' union all
select '2013', '1', '10', '' union all
select '2013', '1', '11', '3.2' union all
select '2013', '1', '12', '3.2' union all
select '2013', '1', '13', '' union all
select '2013', '1', '14', '' union all
select '2013', '1', '15', '' union all
select '2013', '1', '16', '3.1' union all
select '2013', '1', '17', '3.2' union all
select '2013', '1', '18', '3.3' union all
select '2013', '1', '19', '3.4' union all
select '2013', '1', '20', '3.5' union all
select '2013', '1', '21', '' union all
select '2013', '1', '22', '' union all
select '2013', '1', '23', '' union all
select '2013', '1', '24', '' union all
select '2013', '1', '25', '3.6' union all
select '2013', '1', '26', '3.7' union all
select '2013', '1', '27', '3.8' union all
select '2013', '1', '28', '' union all
select '2013', '1', '29', '' union all
select '2013', '1', '30', '3.0' union all
select '2013', '1', '31', '2.8'
select a.年,a.月,
(select top 1 count(1)
from
(select cast(b.日 as int)-row_number() over(order by cast(b.日 as int)) 'x'
from 表a b
where b.年=a.年 and b.月=a.月 and b.降雨量='') t
group by x order by count(1) desc)'连续无降雨的最大天数'
from 表a a
group by a.年,a.月
/*
年 月 连续无降雨的最大天数
---- ---- -----------
2013 1 5
(1 row(s) affected)
*/
DROP TABLE 表a
CREATE TABLE 表a
(年 VARCHAR(4), 月 VARCHAR(2), 日 VARCHAR(2), 降雨量 VARCHAR(5))
INSERT INTO 表a
SELECT '2013', '1', '1', '7.3' UNION ALL
SELECT '2013', '1', '2', '2.8' UNION ALL
SELECT '2013', '1', '3', '' UNION ALL
SELECT '2013', '1', '4', '' UNION ALL
SELECT '2013', '1', '5', '' UNION ALL
SELECT '2013', '1', '6', '' UNION ALL
SELECT '2013', '1', '7', '' UNION ALL
SELECT '2013', '1', '8', '1.5' UNION ALL
SELECT '2013', '2', '1', '3.2' UNION ALL
SELECT '2013', '2', '1', '4.0' UNION ALL
SELECT '2013', '2', '1', ''
SELECT 年,月, start,ends,daycount FROM
(
SELECT
年,月, start = MIN(日), ends = MAX(日), daycount = COUNT(*),
maxday = ROW_NUMBER() OVER(PARTITION BY 年, 月 ORDER BY COUNT(1) DESC)
FROM
(
SELECT 年, 月, 日, grp = 日 - ROW_NUMBER() OVER (PARTITION BY 年, 月 ORDER BY 日)
FROM 表a
WHERE 降雨量 = ''
) T
GROUP BY 年, 月, grp
) D
WHERE maxday = 1
/*
年月startendsdaycount
20131375
20132111
*/
