SQL Server 按照條件統計雨量資料

SQL中擷取目前時間的小時數:

select ltrim(datepart(hh,getdate()));

 

問題條件：

如果目前時間在8時之前，則取昨日8時後的累積雨量，如果在8日之後，則從今日8時之後取資料，SQL語句如下：

 

select t.STNM,t1.DRP,t.LTTD,t.LGTD,t1.STCD from(    select STNM,LTTD,LGTD,STCD from ST_STBPRP_B) t,(      select distinct(STCD) as STCD,ttt.DRP as DRP from ST_PPTN_R tt      left join       (           select STCD as STCD2,sum(DRP) as DRP from ST_PPTN_R where           TM>=            (                case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end            )             group by STCD      ) ttt       on tt.STCD=ttt.STCD2 ) t1 where t.STCD=t1.STCD;

 

select t.STNM,t1.Z,t.LTTD,t.LGTD,t1.STCD from(    select STNM,LTTD,LGTD,STCD,ADDVCD,STTP from ST_STBPRP_B )t,(     select distinct(STCD) as STCD,t3.Z from      (         select STCD,TM,Z from ST_RIVER_R         union          select STCD,TM,RZ as Z from ST_RSVR_R     ) t2      left join     (         select tt.STCD as STCD2, avg(Z) as Z from           (             select STCD,TM,Z from ST_RIVER_R              union               select STCD,TM,RZ as Z from ST_RSVR_R         ) tt         where  tt.TM>=                                                (                        case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end                                                  )                                                group by tt.STCD      ) t3      on t2.STCD = t3.STCD2) t1where  t.STCD = t1.STCD and t.STTP not in ('pp') and t.ADDVCD like '3410%'

--所有雨量網站select d.STNM,( case when d.STCD=e.STCD then e.DRP else 0 end) as DRP,d.LTTD,d.LGTD,d.STCDfrom(select t.STNM,t.LTTD,t.LGTD,t.STCD from ST_STBPRP_B t where STTP='PP' or STCD like '_______4') dleft join(select t.STNM,ISNULL(t1.DRP,0) DRP,t.LTTD,t.LGTD,t1.STCD from(    select STNM,LTTD,LGTD,STCD from ST_STBPRP_B where STTP='PP' or STCD like '_______4') t,(      select distinct(STCD) as STCD,ttt.DRP as DRP from ST_PPTN_R tt      left join       (select STCD as STCD2,sum(DRP) as DRP from ST_PPTN_R whereTM>=(   case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end) group by STCD      ) ttt       on tt.STCD=ttt.STCD2 ) t1 where t.STCD=t1.STCD) eon d.STCD=e.STCD;--所有水位站select d.STNM,(  case when d.STCD=e.STCD then e.Z else 0 end) as Z,d.LTTD,d.LGTD,d.STCDfrom( select t.STNM,t.LTTD,t.LGTD,t.STCD from ST_STBPRP_B t where STTP<>'PP') dleft join( select t.STNM,t1.Z,t.LTTD,t.LGTD,t1.STCD from (      select STNM,LTTD,LGTD,STCD from ST_STBPRP_B  )t , (       select * from   (     select STCD,TM,Z from ST_RIVER_R    union                select STCD,TM,RZ as Z from ST_RSVR_R    ) t  where t.TM in    (        select MAX(TM) from       (            select STCD,TM,Z from ST_RIVER_R       union        select STCD,TM,RZ as Z from ST_RSVR_R        ) t1     where t.STCD=t1.STCD    )   ) t1 where  t.STCD = t1.STCD) eon d.STCD=e.STCD;

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

 

--警示水庫資料create view v_SK_WarningInfoasselect zdt8 as s,tmnow as slasttime,t.STCD as 水位站編碼,STNM as 水位站名稱,STLC as 所屬縣,LGTD,LTTD,ShowLevel,WRZ,GRZ,tt.STTP,tt.RVNM,t.Msgfrom(     select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(WRZ,0) as WRZ,IsNull(GRZ,0) as GRZ,case when WRZ-zdt8=0 then '達警戒' when zdt8 between WRZ and GRZ then '超警戒:'+cast((zdt8-WRZ) as varchar(10))  when zdt8>GRZ then '超保證:'+cast((zdt8-GRZ) as varchar(10)) else '正常' end as Msg from ST_RIVER_D     union    select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(FSLTDZ,0) as WRZ,0 as GRZ,case when FSLTDZ-zdt8=0 then '達汛限'  when zdt8>FSLTDZ then '超汛限:'+cast((zdt8-FSLTDZ) as varchar(10)) else '正常' end as Msg from ST_RSVR_D) t,(    select STCD,STTP,RVNM from ST_STBPRP_B where STTP='RR') ttwhere t.STCD=tt.STCD and Msg<>'正常';--警示河道資料create view v_HD_WarningInfoasselect zdt8 as s,tmnow as slasttime,t.STCD as 水位站編碼,STNM as 水位站名稱,STLC as 所屬縣,LGTD,LTTD,ShowLevel,WRZ,GRZ,tt.STTP,tt.RVNM,t.Msgfrom(     select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(WRZ,0) as WRZ,IsNull(GRZ,0) as GRZ,case when WRZ-zdt8=0 then '達警戒' when zdt8 between WRZ and GRZ then '超警戒:'+cast((zdt8-WRZ) as varchar(10))  when zdt8>GRZ then '超保證:'+cast((zdt8-GRZ) as varchar(10)) else '正常' end as Msg from ST_RIVER_D     union    select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(FSLTDZ,0) as WRZ,0 as GRZ,case when FSLTDZ-zdt8=0 then '達汛限'  when zdt8>FSLTDZ then '超汛限:'+cast((zdt8-FSLTDZ) as varchar(10)) else '正常' end as Msg from ST_RSVR_D) t,(    select STCD,STTP,RVNM from ST_STBPRP_B where STTP='ZZ') ttwhere t.STCD=tt.STCD and Msg<>'正常';