系统每6分钟向数据添加一条记录,我想要在添加完一小时的记录后,自动累计这一小时的数据,写进另一个表,请问这个触发器怎么实现,还是得用其他的?时间 数据
2007-05-08 00:00:00.000 2
2007-05-08 00:06:00.000 4
2007-05-08 00:12:00.000 3
2007-05-08 00:18:00.000 6
2007-05-08 00:24:00.000 7
2007-05-08 00:30:00.000 5
2007-05-08 00:36:00.000 6
2007-05-08 00:42:00.000 3
2007-05-08 00:48:00.000 3
2007-05-08 00:54:00.000 2
2007-05-08 01:00:00.000 6
2007-05-08 01:06:00.000 3
2007-05-08 01:12:00.000 1
2007-05-08 01:18:00.000 5
2007-05-08 00:00:00.000 2
2007-05-08 00:06:00.000 4
2007-05-08 00:12:00.000 3
2007-05-08 00:18:00.000 6
2007-05-08 00:24:00.000 7
2007-05-08 00:30:00.000 5
2007-05-08 00:36:00.000 6
2007-05-08 00:42:00.000 3
2007-05-08 00:48:00.000 3
2007-05-08 00:54:00.000 2
2007-05-08 01:00:00.000 6
2007-05-08 01:06:00.000 3
2007-05-08 01:12:00.000 1
2007-05-08 01:18:00.000 5
for insert
as
declare @Min datetime
declare @Max datetimeselect @Min=min(时间),@Max=max(时间) from [Table] where flag=0
if datediff(hour,@Min,@Max)=1
begin
insert [新表] select sum(数据) from [Table] where 时间 between @Min and @Max
update [Table] set flag=1 where 时间 between @Min and @Max
end
select datediff(mi,'2007-05-08 23:54:00.000 ','2007-05-09 00:06:00.000 ')select datediff(mi,'2007-05-08 23:54:00.000 ','2007-05-09 00:07:00.000 ')
--
结果
12
13select datediff(hh,'2007-05-08 23:54:00.000 ','2007-05-09 00:06:00.000 ')select datediff(hh,'2007-05-08 23:54:00.000 ','2007-05-09 00:07:00.000 ')结果
1
1
lz 把if datediff(hh,@Min,@Max)=1 改成if datediff(mi,@Min,@Max)=60 试下呢