CREATE procedure QueryACCP @StartTime datetime,@EndTime datetime as declare @STCD char(8) declare @ACCP float create table #Result(STCD CHAR(8) ,ACCP float) declare STCDCursor cursor for select distinct STCD from ViewHourQuery where TM between @StartTime and @EndTime
open STCDCursor while(0=0) begin fetch next from STCDCursor into @STCD if(@@FETCH_STATUS <>0) BREAK set @ACCP=dbo.TotalACCP(@STCD,@StartTime,@EndTime) insert into #Result values(@STCD,@ACCP) end close STCDCursor deallocate STCDCursor select a.STCD,a.STNM,b.ADDVNM,c.ACCP from (ST_STBPRP_B a left outer join ST_ADDVCD_B B on a.ADDVCD=b.ADDVCD)inner join #Result c on a.STCD=c.STCD drop table #Result
这是我写的存储过程，查询一段时间以内的各个站点的降雨量，STCD是站点号，ACCP是降雨量，最后连接2张表，投影STNM（站点名称），ADDVNM（站点所属城市名称） TotalACCP函数是计算一个STCD的降雨量，里面用到了其他的几个函数 因为表中的记录不是连续的，有的没有进行统计，如某一天的记录没有，则调用NotExistDay求 NotExist1stXun（上旬记录没有）、NotExist2stXun（中旬记录没有）、NotExist3stXun（下旬记录没有）、NotExistMonth(月记录没有)，计算的过程是：如果时间段跨月，则先在月表里去记录，该月记录不存在，则调用NotExistMonth求之，再在剩余的时间段里，够得上旬的则在旬表里取（旬分上、中、下），不够的则调用相应的函数求，再按同样的思路在天表里，最后在到时段表（给时间分段，取值再合计） 1.create function NotExistDay(@STCD CHAR(8),@Time datetime)---统计日降雨量函数 returns float as begin declare @number float set @number=0 select @number=isnull(sum(DRP),0)from ViewHourQuery where STCD=@STCD and TM between dateadd(dd,datediff(dd,0,@Time),0)and dateadd(ms,-3,dateadd(dd,datediff(dd,0,@Time),0)) return @number end
select b.stcd,b.stnm,c.addvnm,sum(a.drp)
from viewhourquery a
leftjoin st_stbprp_b b on a.stcd=b.stcd
leftjoin st_addvcd_b c on b.addvcd=c.addvcd
where a.tm >=dateadd(dd,datediff(dd,0,@Time),0) and a.tm <dateadd(dd,datediff(dd,0,@Time)+1,0)
groupby b.stcd,b.stnm,c.addvnm
CREATE procedure QueryACCP @StartTime datetime,@EndTime datetime as declare @STCD char(8) declare @ACCP float declare @Result table (STCD CHAR(8) primary key,ACCP float) insert @Result select STCD,accp_new = isnull(sum(DRP),0) from ViewHourQuery(index=idx_vq_tm)--这个你要建好 再建pk_stcd_tm比较一下用(index=pk_stcd_tm) where TM between @StartTime and @EndTime group by stcd
select a.STCD,a.STNM,b.ADDVNM,c.ACCP from ST_STBPRP_B a(index=pk_ST_STBPRP_B_stcd)--如果可以,把stcd建成ST_STBPRP_B主键或聚集索引 inner join @Result c(index=pk_stcd_tm) on a.STCD=c.STCD left outer join ST_ADDVCD_B B on a.ADDVCD=b.ADDVCD
好了，现在搞定了，多亏了flairsky的帮助： 批量过滤，过程如下： 先找出在月表中有记录的站点 select STCD,IDTM,ACCP into #Month from ViewMonthQuery where IDTM BETWEEN ** AND ** AND stcd IN (SELECT DISTINCT stcd from ViewHourQuery where TM between ** and **)
再在上旬表记录塞选，在这个时间段有记录，并且月份不和表记录的月份相同 select STCD,IDTM ,ACCP INTO #1stXun from View1stXunQuery a where IDTM BETWEEN ** AND ** AND stcd IN (SELECT DISTINCT stcd from ViewHourQuery where TM between ** and **) and not exists(select * from #Month b where a.STCD=b.STCD and datepart(yy,a.IDTM)=datepart(yy,b.IDTM)and datepart(mm,a.IDTM)=datepart(mm,b.IDTM))
其他类似，最后分组求和 select STCD,SUM(ACCP)as ACCP from ** group by STCD
