有表如下:
T_Data(DataTime,Press,Flux)系统每分钟保存一条记录,现需得到如下结果:
每三钟的平均Press,与该三钟的最后一个Flux,作为一条记录保存到另一张表,该条记录的时间也是取三条中的最后一条。
求此SQL!实例如:
DataTime Press Flux
2006-8-8 15:24:00 35 2342
2006-8-8 15:25:00 23 3456
2006-8-8 15:26:00 565 345
2006-8-8 15:27:00 37 634
2006-8-8 15:28:00 98 234
2006-8-8 15:29:00 2 437
2006-8-8 15:30:00 354 3653
2006-8-8 15:31:00 66 21341
2006-8-8 15:32:00 67 134
2006-8-8 15:33:00 875 2352
2006-8-8 15:34:00 355 2452
2006-8-8 15:35:00 35 23452
2006-8-8 15:36:00 385 2452
2006-8-8 15:37:00 35 2435234即24-26分钟的数据统计为一条
27-29分钟的数据统计为一条,不知我说清楚了没有?急求,谢谢!
T_Data(DataTime,Press,Flux)系统每分钟保存一条记录,现需得到如下结果:
每三钟的平均Press,与该三钟的最后一个Flux,作为一条记录保存到另一张表,该条记录的时间也是取三条中的最后一条。
求此SQL!实例如:
DataTime Press Flux
2006-8-8 15:24:00 35 2342
2006-8-8 15:25:00 23 3456
2006-8-8 15:26:00 565 345
2006-8-8 15:27:00 37 634
2006-8-8 15:28:00 98 234
2006-8-8 15:29:00 2 437
2006-8-8 15:30:00 354 3653
2006-8-8 15:31:00 66 21341
2006-8-8 15:32:00 67 134
2006-8-8 15:33:00 875 2352
2006-8-8 15:34:00 355 2452
2006-8-8 15:35:00 35 23452
2006-8-8 15:36:00 385 2452
2006-8-8 15:37:00 35 2435234即24-26分钟的数据统计为一条
27-29分钟的数据统计为一条,不知我说清楚了没有?急求,谢谢!
Insert T_Data Select '2006-8-8 15:24:00', 35, 2342
Union All Select '2006-8-8 15:25:00', 23, 3456
Union All Select '2006-8-8 15:26:00', 565, 345
Union All Select '2006-8-8 15:27:00', 37, 634
Union All Select '2006-8-8 15:28:00', 98, 234
Union All Select '2006-8-8 15:29:00', 2, 437
Union All Select '2006-8-8 15:30:00', 354, 3653
Union All Select '2006-8-8 15:31:00', 66, 21341
Union All Select '2006-8-8 15:32:00', 67, 134
Union All Select '2006-8-8 15:33:00', 875, 2352
Union All Select '2006-8-8 15:34:00', 355, 2452
Union All Select '2006-8-8 15:35:00', 35, 23452
Union All Select '2006-8-8 15:36:00', 385, 2452
Union All Select '2006-8-8 15:37:00', 35, 2435234
GO
Declare @MinDataTime DateTime
Select @MinDataTime=Min(DataTime) From T_Data
Select
A.DataTime,
B.Press,
A.Flux
From T_Data A
Inner Join
(Select
Avg(Press) As Press,
Max(DataTime) As DataTime
From T_Data
Group By DateDiff(mi,@MinDataTime,DataTime)/3) B
On A.DataTime=B.DataTime
GO
Drop Table T_Data
/*
DataTime Press Flux
2006-08-08 15:26:00.000 207 345
2006-08-08 15:29:00.000 45 437
2006-08-08 15:32:00.000 162 134
2006-08-08 15:35:00.000 421 23452
2006-08-08 15:37:00.000 210 2435234
*/
into #t
from T_Data
where ......
order by DataTime
Press Fluxselect (select avg(Press) from #t where id \ 3=a.id \3) as avgPress, Flux
from #t
where id % 3=2drop table #t
into #t
from T_Data
order by DataTime select (select avg(Press) from #t where id / 3=a.id /3) as avgPress, Flux
from #t a
where id % 3=2drop table #t--结果
avgPress Flux
----------- -----------
207 345
45 437
162 134
421 23452(所影响的行数为 4 行)