自己解决了,分别将查询一个时间段的结果集定义为一张表,例如5分钟的为t1,15分钟的为t2....,然后from 所有表 where 表1.id=表2.id...,将所有表连接起来
declare @tab table(id int, sensorid int, [date] datetime, value numeric(10,3)); insert into @tab select 316310,810,'2010-7-5 13:53:27',0.328 union all select 316311,810,'2010-7-5 13:54:27',0.326 union all select 316312,810,'2010-7-5 13:55:27',0.329 union all select 316313,810,'2010-7-5 13:56:27',0.328 union all select 316314,810,'2010-7-5 13:57:27',0.325 union all select 316315,810,'2010-7-5 13:58:27',0.324 union all select 316316,810,'2010-7-5 13:59:27',0.326 union all select 316317,810,'2010-7-5 14:00:27',0.328 union all select 316318,810,'2010-7-5 14:01:27',0.33 union all select 316319,810,'2010-7-5 14:02:27',0.325; declare @now datetime; set @now='2010-7-5 14:05:00'; select SENSORID, max(case when [date] between dateadd(mi,-5,@now) and @now then value end)- min(case when [date] between dateadd(mi,-5,@now) and @now then value end) [5M], max(case when [date] between dateadd(mi,-15,@now) and @now then value end)- min(case when [date] between dateadd(mi,-15,@now) and @now then value end) [15M], max(case when [date] between dateadd(mi,-30,@now) and @now then value end)- min(case when [date] between dateadd(mi,-30,@now) and @now then value end) [30M], max(case when [date] between dateadd(mi,-60,@now) and @now then value end)- min(case when [date] between dateadd(mi,-60,@now) and @now then value end) [60M] from @tab group by SENSORID;
set @duration=5
select MAX(VALUE)-MIN(VALUE) from tb
where TIME>=DATEDIFF(mi,-1*@duration,GETDATE())
?
316310 810 2010-7-5 13:53:27 0.328
316311 810 2010-7-5 13:54:27 0.326
316312 810 2010-7-5 13:55:27 0.329
316313 810 2010-7-5 13:56:27 0.328
316314 810 2010-7-5 13:57:27 0.325
316315 810 2010-7-5 13:58:27 0.324
316316 810 2010-7-5 13:59:27 0.326
316317 810 2010-7-5 14:00:27 0.328
316318 810 2010-7-5 14:01:27 0.33
316319 810 2010-7-5 14:02:27 0.325期望得到结果:SENSORID FIVEMUNITE FIFTEENMUNITE HALFHOURMUNITE HOURMUNITE ...
810
where time>=datediff(mi,-5,getdate())
SENSORID FIVEMUNITE FIFTEENMUNITE HALFHOUR HOUR
801 0.002 0.003 0.004 0.005
declare @tab table(id int, sensorid int, [date] datetime, value numeric(10,3));
insert into @tab
select 316310,810,'2010-7-5 13:53:27',0.328 union all
select 316311,810,'2010-7-5 13:54:27',0.326 union all
select 316312,810,'2010-7-5 13:55:27',0.329 union all
select 316313,810,'2010-7-5 13:56:27',0.328 union all
select 316314,810,'2010-7-5 13:57:27',0.325 union all
select 316315,810,'2010-7-5 13:58:27',0.324 union all
select 316316,810,'2010-7-5 13:59:27',0.326 union all
select 316317,810,'2010-7-5 14:00:27',0.328 union all
select 316318,810,'2010-7-5 14:01:27',0.33 union all
select 316319,810,'2010-7-5 14:02:27',0.325;
declare @now datetime;
set @now='2010-7-5 14:05:00';
select SENSORID,
max(case when [date] between dateadd(mi,-5,@now) and @now then value end)-
min(case when [date] between dateadd(mi,-5,@now) and @now then value end) [5M],
max(case when [date] between dateadd(mi,-15,@now) and @now then value end)-
min(case when [date] between dateadd(mi,-15,@now) and @now then value end) [15M],
max(case when [date] between dateadd(mi,-30,@now) and @now then value end)-
min(case when [date] between dateadd(mi,-30,@now) and @now then value end) [30M],
max(case when [date] between dateadd(mi,-60,@now) and @now then value end)-
min(case when [date] between dateadd(mi,-60,@now) and @now then value end) [60M]
from @tab group by SENSORID;