求一SQL算法?

Question

TAB1表
站号       时间                 雨量
M1554 2010-05-03 14:10:00.000 0
M1554 2010-05-03 14:20:00.000 1
M1554 2010-05-03 14:30:00.000 5
M1554 2010-05-03 14:40:00.000 13
M1554 2010-05-03 14:50:00.000 14
M1554 2010-05-03 15:00:00.000 14
M1554 2010-05-03 15:10:00.000 0
M1554 2010-05-03 15:20:00.000 0
M1554 2010-05-03 15:30:00.000 8
M1554 2010-05-03 15:40:00.000 51
M1554 2010-05-03 15:50:00.000 60
M1554 2010-05-03 16:00:00.000 62
M1554 2010-05-03 16:10:00.000 2
M1554 2010-05-03 16:20:00.000 5
M1554 2010-05-03 16:30:00.000 5
M1554 2010-05-03 16:40:00.000 5
M1554 2010-05-03 16:50:00.000 5
M1554 2010-05-03 17:00:00.000 5每1小时雨量都是从第1个10分钟开始累加，然后在正点时的雨量值才为每1小时雨量总值,为了解释清楚我把上表手工分解为每10分钟雨量表为
站号       时间                 10分钟雨量
M1554 2010-05-03 14:10:00.000 0
M1554 2010-05-03 14:20:00.000 1-0=1
M1554 2010-05-03 14:30:00.000 (5-1)=4
M1554 2010-05-03 14:40:00.000 (13-5)=8
M1554 2010-05-03 14:50:00.000 (14-13)=1
M1554 2010-05-03 15:00:00.000 14-14=0
M1554 2010-05-03 15:10:00.000 0
M1554 2010-05-03 15:20:00.000 2-0=2
M1554 2010-05-03 15:30:00.000 8-2=6
M1554 2010-05-03 15:40:00.000 51-8=43
M1554 2010-05-03 15:50:00.000 60-51=9
M1554 2010-05-03 16:00:00.000 62-60=2
M1554 2010-05-03 16:10:00.000 2
M1554 2010-05-03 16:20:00.000 5-2=3
M1554 2010-05-03 16:30:00.000 5-5=0
M1554 2010-05-03 16:40:00.000 5-5=0
M1554 2010-05-03 16:50:00.000 7-5=2
M1554 2010-05-03 17:00:00.000 7-7=0我现在想统计近2小时的雨量即如统计2010-05-03 14:30:00.000至2010-05-03 15:20:00.000的雨量总和为多少?该SQL算法怎么写？
M1554 2010-05-03 14:30:00.000 4
M1554 2010-05-03 14:40:00.000 8
M1554 2010-05-03 14:50:00.000 1
M1554 2010-05-03 15:00:00.000 0
M1554 2010-05-03 15:10:00.000 0
M1554 2010-05-03 15:20:00.000 2
这里总和为15,但通过TAB1表SQL算法怎么写？

Answer 1

TAB1表
站号 时间 雨量
M1554 2010-05-03 14:10:00.000 0
M1554 2010-05-03 14:20:00.000 1
M1554 2010-05-03 14:30:00.000 5
M1554 2010-05-03 14:40:00.000 13
M1554 2010-05-03 14:50:00.000 14
M1554 2010-05-03 15:00:00.000 14
M1554 2010-05-03 15:10:00.000 0
M1554 2010-05-03 15:20:00.000 2
M1554 2010-05-03 15:30:00.000 8
M1554 2010-05-03 15:40:00.000 51
M1554 2010-05-03 15:50:00.000 60
M1554 2010-05-03 16:00:00.000 62
M1554 2010-05-03 16:10:00.000 2
M1554 2010-05-03 16:20:00.000 5
M1554 2010-05-03 16:30:00.000 5
M1554 2010-05-03 16:40:00.000 5
M1554 2010-05-03 16:50:00.000 7
M1554 2010-05-03 17:00:00.000 7
这个才是正确的

Answer 2

create table tb(站号 varchar(10),时间 datetime,雨量 int)
insert into tb values('M1554' ,'2010-05-03 14:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 14:20:00.000', 1)
insert into tb values('M1554' ,'2010-05-03 14:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 14:40:00.000', 13)
insert into tb values('M1554' ,'2010-05-03 14:50:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:00:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 15:20:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 15:30:00.000', 8)
insert into tb values('M1554' ,'2010-05-03 15:40:00.000', 51)
insert into tb values('M1554' ,'2010-05-03 15:50:00.000', 60)
insert into tb values('M1554' ,'2010-05-03 16:00:00.000', 62)
insert into tb values('M1554' ,'2010-05-03 16:10:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 16:20:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:40:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:50:00.000', 7)
insert into tb values('M1554' ,'2010-05-03 17:00:00.000', 7)
go
declare @dt as datetime
set @dt = '2010-05-03 15:21:00.000'select sum(val) from
(
select t.* , val = isnull(雨量 - (select top 1 雨量 from tb where 时间 <= @dt and 时间>= dateadd(hh,-1,convert(varchar(14),@dt,120) + ltrim(datepart(mi,@dt) - datepart(mi,@dt) % 10) + ':00') and 站号 = t.站号 and 时间 < t.时间 order by 时间 desc),0)
from tb t 
where 时间 <= @dt and 时间>= dateadd(hh,-1,convert(varchar(14),@dt,120) + ltrim(datepart(mi,@dt) - datepart(mi,@dt) % 10) + ':00')
) m
where right(convert(varchar(8),时间,108),5) not in ('10:00','00:00')drop table tb/*
            
----------- 
15（所影响的行数为 1 行）*/

Answer 3

2个小时呢怎么算?
我现在想统计近2小时的雨量即如统计2010-05-03 14:40:00.000至2010-05-03 16:30:00.000的雨量总和为多少?该SQL算法怎么写？
M1554 2010-05-03 14:40:00.000 8
M1554 2010-05-03 14:50:00.000 1
M1554 2010-05-03 15:00:00.000 0
M1554 2010-05-03 15:10:00.000 0
M1554 2010-05-03 15:20:00.000 2
M1554 2010-05-03 15:30:00.000 6
M1554 2010-05-03 15:40:00.000 43
M1554 2010-05-03 15:50:00.000 9
M1554 2010-05-03 16:00:00.000 2
M1554 2010-05-03 16:10:00.000 2
M1554 2010-05-03 16:20:00.000 3
M1554 2010-05-03 16:30:00.000 0得到结果为76

Answer 4

我明白你的意思了,其实你只要按照如下设置时间值即可,不管是多少个小时.
但是,前提是时间必须是10钟的整数倍.
create table tb(站号 varchar(10),时间 datetime,雨量 int)
insert into tb values('M1554' ,'2010-05-03 14:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 14:20:00.000', 1)
insert into tb values('M1554' ,'2010-05-03 14:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 14:40:00.000', 13)
insert into tb values('M1554' ,'2010-05-03 14:50:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:00:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 15:20:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 15:30:00.000', 8)
insert into tb values('M1554' ,'2010-05-03 15:40:00.000', 51)
insert into tb values('M1554' ,'2010-05-03 15:50:00.000', 60)
insert into tb values('M1554' ,'2010-05-03 16:00:00.000', 62)
insert into tb values('M1554' ,'2010-05-03 16:10:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 16:20:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:40:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:50:00.000', 7)
insert into tb values('M1554' ,'2010-05-03 17:00:00.000', 7)
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2010-05-03 14:30:00.000'
set @dt2 = '2010-05-03 15:20:00.000'select sum(val) from
(
select t.* , val = (case when datepart(mi,时间) = 10 then 雨量 else isnull(雨量 - (select top 1 雨量 from tb where 时间 between dateadd(mi,-10,@dt1) and @dt2 and 站号 = t.站号 and 时间 < t.时间 order by 时间 desc),0) end)
from tb t 
where 时间 between dateadd(mi,-10,@dt1) and @dt2
) m
/*
            
----------- 
15（所影响的行数为 1 行）
*/set @dt1 = '2010-05-03 14:40:00.000'
set @dt2 = '2010-05-03 16:30:00.000'select sum(val) from
(
select t.* , val = (case when datepart(mi,时间) = 10 then 雨量 else isnull(雨量 - (select top 1 雨量 from tb where 时间 between dateadd(mi,-10,@dt1) and @dt2 and 站号 = t.站号 and 时间 < t.时间 order by 时间 desc),0) end)
from tb t 
where 时间 between dateadd(mi,-10,@dt1) and @dt2
) m
/*
            
----------- 
76（所影响的行数为 1 行）
*/drop table tb/*
            
----------- 
15（所影响的行数为 1 行）*/

Answer 5

create table tb(站号 varchar(10),时间 datetime,雨量 int)
insert into tb values('M1554' ,'2010-05-03 14:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 14:20:00.000', 1)
insert into tb values('M1554' ,'2010-05-03 14:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 14:40:00.000', 13)
insert into tb values('M1554' ,'2010-05-03 14:50:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:00:00.000', 14)
insert into tb values('M1554' ,'2010-05-03 15:10:00.000', 0)
insert into tb values('M1554' ,'2010-05-03 15:20:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 15:30:00.000', 8)
insert into tb values('M1554' ,'2010-05-03 15:40:00.000', 51)
insert into tb values('M1554' ,'2010-05-03 15:50:00.000', 60)
insert into tb values('M1554' ,'2010-05-03 16:00:00.000', 62)
insert into tb values('M1554' ,'2010-05-03 16:10:00.000', 2)
insert into tb values('M1554' ,'2010-05-03 16:20:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:30:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:40:00.000', 5)
insert into tb values('M1554' ,'2010-05-03 16:50:00.000', 7)
insert into tb values('M1554' ,'2010-05-03 17:00:00.000', 7)
godeclare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2010-05-03 14:30:00.000'
set @dt2 = '2010-05-03 15:20:00.000'
;with cte as(
select *,convert(char(13),dateadd(mi,-1,时间),120) as grp
,rn=ROW_NUMBER() over(partition by 站号,convert(char(13),dateadd(mi,-1,时间),120) order by dateadd(mi,-1,时间) ) 
from tb
)
select sum(a.雨量-ISNULL(b.雨量,0)) as 雨量总和 from cte a 
left join cte b on a.grp=b.grp and a.rn=b.rn+1
where  a.时间 between  @dt1 and @dt2
           
       雨量总和
-----------
         15(1 行受影响)