sql server 一年内连续降雨几天及开始结束日期

 select
     min(tm) 'startday',
     max(tm) 'endday',
     count(1) 'lxday',
     SUM(avgz) avgz
 from
 (

  select a.tm,a.avgz,
   datepart(Y,a.tm)-
     (
       select count(1) from
      (
     select * from (
select datepart(mm,ts.TM) as mm,  datepart(dd,ts.TM) as dd,avg(ts.DRP) as avgz,ts.tm from ST_PPTN_DAY_R ts left join ST_STBPRP_B f on ts.STCD = f.stcd where ts.TM>='2016-01-01 00:00:00' and ts.TM<'2016-12-31 23:59:59'  group by datepart(mm,ts.TM) ,datepart(dd,ts.TM),ts.tm
) ag  where avgz >0

      ) b
     where  b.tm<=a.tm
        
   ) 'rn'
    from
    (
     select * from (
select datepart(mm,ts.TM) as mm,  datepart(dd,ts.TM) as dd,avg(ts.DRP) as avgz,ts.tm from ST_PPTN_DAY_R ts left join ST_STBPRP_B f on ts.STCD = f.stcd where ts.TM>='2016-01-01 00:00:00' and ts.TM<'2016-12-31 23:59:59'  group by datepart(mm,ts.TM) ,datepart(dd,ts.TM),ts.tm
) ag  where avgz >0

    )a
   
 
 ) t
 
 group by rn;

 

8个雨量站的平均降雨量

select datepart(mm,ts.TM) as mm,  datepart(dd,ts.TM) as dd,avg(ts.DRP) as avgz,ts.tm from ST_PPTN_DAY_R ts left join ST_STBPRP_B f on ts.STCD = f.stcd where ts.TM>='2016-01-01 00:00:00' and ts.TM<'2016-12-31 23:59:59' group by datepart(mm,ts.TM) ,datepart(dd,ts.TM),ts.tm