根据类型、时间求一列数据的差
Ark_ID int4
Up_Time datetime
Temperature_Ark numeric9
Ark_ID Up_Time Temperature_Ark
12011-11-23 7:50:02258452.25
22011-11-23 7:50:02201406.7
32011-11-23 7:50:02257207.78
42011-11-23 7:50:02262221.55
52011-11-23 7:50:02377021.81
62011-11-23 7:50:02150993.8
72011-11-23 7:50:024060757.56
82011-11-23 7:50:0245649.9
12011-11-23 8:00:02258452.77
22011-11-23 8:00:02201408.04
32011-11-23 8:00:02257209.57
42011-11-23 8:00:02262222.54
52011-11-23 8:00:02377022.72
62011-11-23 8:00:02150994.81
72011-11-23 8:00:024060758.16
82011-11-23 8:00:0245650.02
12011-11-23 8:10:02258453.55
22011-11-23 8:10:02201409.22
32011-11-23 8:10:02257211.97
42011-11-23 8:10:02262223.86
52011-11-23 8:10:02377023.89
62011-11-23 8:10:02150996.19
72011-11-23 8:10:024060758.73
82011-11-23 8:10:0245650.1
12011-11-23 8:20:02258454.12
22011-11-23 8:20:02201410.65
32011-11-23 8:20:02257214.86
42011-11-23 8:20:02262225.7
52011-11-23 8:20:02377025.42
62011-11-23 8:20:02150997.86
72011-11-23 8:20:024060759.13
82011-11-23 8:20:0245650.22
12011-11-23 8:30:02258454.85
22011-11-23 8:30:02201412.42
32011-11-23 8:30:02257217.63
42011-11-23 8:30:02262227.86
52011-11-23 8:30:02377027.19
62011-11-23 8:30:02150999.21
72011-11-23 8:30:024060759.64
82011-11-23 8:30:0245650.35
12011-11-23 8:40:02258455.94
22011-11-23 8:40:02201414.1
32011-11-23 8:40:02257220.36
42011-11-23 8:40:02262230.37
52011-11-23 8:40:02377029.9
62011-11-23 8:40:02151000.57
72011-11-23 8:40:024060760.18
82011-11-23 8:40:0245650.61
12011-11-23 8:50:02258457.18
22011-11-23 8:50:02201415.6
32011-11-23 8:50:02257223.05
42011-11-23 8:50:02262232.65
52011-11-23 8:50:02377032.48
62011-11-23 8:50:02151002.01
72011-11-23 8:50:024060760.75
82011-11-23 8:50:0245650.72
12011-11-23 9:00:02258458.99
22011-11-23 9:00:02201417.48
32011-11-23 9:00:02257225.87
42011-11-23 9:00:02262234.79
52011-11-23 9:00:02377035.13
62011-11-23 9:00:02151003.65
72011-11-23 9:00:024060761.32
82011-11-23 9:00:0245650.86
12011-11-23 9:10:02258460.32
22011-11-23 9:10:02201419.08
32011-11-23 9:10:02257228.73
42011-11-23 9:10:02262236.89
52011-11-23 9:10:02377037.73
62011-11-23 9:10:02151005.07
72011-11-23 9:10:024060761.88
82011-11-23 9:10:0245651.02
根据Ark_ID、Up_Time这二个变量计算Temperature_Ark差值，根据8个类型Ark_ID每一天计算一次Temperature_Ark，只要计算这一天的最后一条数据减去一天最开始的数据，求那个好人帮忙
[解决办法]
select Ark_ID, convert(varchar(10),Up_Time,120) , sum(Temperature_Ark) from(select t.* from tb t where Up_Time = (select max(Up_Time) from tb where Ark_ID = t.Ark_ID and datediff(dd,Up_Time,t.Up_Time) = 0)union allselect t.Ark_ID ,t.Up_Time ,-t.Temperature_Ark from tb t where Up_Time = (select min(Up_Time) from tb where Ark_ID = t.Ark_ID and datediff(dd,Up_Time,t.Up_Time) = 0)) kgroup by Ark_ID, convert(varchar(10),Up_Time,120)
[解决办法]
create table tb(Ark_ID int,Up_Time datetime,Temperature_Ark decimal(9,2))insert into tb select 1,'2011-11-23 7:50:02',258452.25insert into tb select 2,'2011-11-23 7:50:02',201406.7insert into tb select 3,'2011-11-23 7:50:02',257207.78insert into tb select 4,'2011-11-23 7:50:02',262221.55insert into tb select 5,'2011-11-23 7:50:02',377021.81insert into tb select 6,'2011-11-23 7:50:02',150993.8insert into tb select 7,'2011-11-23 7:50:02',4060757.56insert into tb select 8,'2011-11-23 7:50:02',45649.9insert into tb select 1,'2011-11-23 8:00:02',258452.77insert into tb select 2,'2011-11-23 8:00:02',201408.04insert into tb select 3,'2011-11-23 8:00:02',257209.57insert into tb select 4,'2011-11-23 8:00:02',262222.54insert into tb select 5,'2011-11-23 8:00:02',377022.72insert into tb select 6,'2011-11-23 8:00:02',150994.81insert into tb select 7,'2011-11-23 8:00:02',4060758.16insert into tb select 8,'2011-11-23 8:00:02',45650.02insert into tb select 1,'2011-11-23 8:10:02',258453.55insert into tb select 2,'2011-11-23 8:10:02',201409.22insert into tb select 3,'2011-11-23 8:10:02',257211.97insert into tb select 4,'2011-11-23 8:10:02',262223.86insert into tb select 5,'2011-11-23 8:10:02',377023.89insert into tb select 6,'2011-11-23 8:10:02',150996.19insert into tb select 7,'2011-11-23 8:10:02',4060758.73insert into tb select 8,'2011-11-23 8:10:02',45650.1insert into tb select 1,'2011-11-23 8:20:02',258454.12insert into tb select 2,'2011-11-23 8:20:02',201410.65insert into tb select 3,'2011-11-23 8:20:02',257214.86insert into tb select 4,'2011-11-23 8:20:02',262225.7insert into tb select 5,'2011-11-23 8:20:02',377025.42insert into tb select 6,'2011-11-23 8:20:02',150997.86insert into tb select 7,'2011-11-23 8:20:02',4060759.13insert into tb select 8,'2011-11-23 8:20:02',45650.22insert into tb select 1,'2011-11-23 8:30:02',258454.85insert into tb select 2,'2011-11-23 8:30:02',201412.42insert into tb select 3,'2011-11-23 8:30:02',257217.63insert into tb select 4,'2011-11-23 8:30:02',262227.86insert into tb select 5,'2011-11-23 8:30:02',377027.19insert into tb select 6,'2011-11-23 8:30:02',150999.21insert into tb select 7,'2011-11-23 8:30:02',4060759.64insert into tb select 8,'2011-11-23 8:30:02',45650.35insert into tb select 1,'2011-11-23 8:40:02',258455.94insert into tb select 2,'2011-11-23 8:40:02',201414.1insert into tb select 3,'2011-11-23 8:40:02',257220.36insert into tb select 4,'2011-11-23 8:40:02',262230.37insert into tb select 5,'2011-11-23 8:40:02',377029.9insert into tb select 6,'2011-11-23 8:40:02',151000.57insert into tb select 7,'2011-11-23 8:40:02',4060760.18insert into tb select 8,'2011-11-23 8:40:02',45650.61insert into tb select 1,'2011-11-23 8:50:02',258457.18insert into tb select 2,'2011-11-23 8:50:02',201415.6insert into tb select 3,'2011-11-23 8:50:02',257223.05insert into tb select 4,'2011-11-23 8:50:02',262232.65insert into tb select 5,'2011-11-23 8:50:02',377032.48insert into tb select 6,'2011-11-23 8:50:02',151002.01insert into tb select 7,'2011-11-23 8:50:02',4060760.75insert into tb select 8,'2011-11-23 8:50:02',45650.72insert into tb select 1,'2011-11-23 9:00:02',258458.99insert into tb select 2,'2011-11-23 9:00:02',201417.48insert into tb select 3,'2011-11-23 9:00:02',257225.87insert into tb select 4,'2011-11-23 9:00:02',262234.79insert into tb select 5,'2011-11-23 9:00:02',377035.13insert into tb select 6,'2011-11-23 9:00:02',151003.65insert into tb select 7,'2011-11-23 9:00:02',4060761.32insert into tb select 8,'2011-11-23 9:00:02',45650.86insert into tb select 1,'2011-11-23 9:10:02',258460.32insert into tb select 2,'2011-11-23 9:10:02',201419.08insert into tb select 3,'2011-11-23 9:10:02',257228.73insert into tb select 4,'2011-11-23 9:10:02',262236.89insert into tb select 5,'2011-11-23 9:10:02',377037.73insert into tb select 6,'2011-11-23 9:10:02',151005.07insert into tb select 7,'2011-11-23 9:10:02',4060761.88insert into tb select 8,'2011-11-23 9:10:02',45651.02goselect a.Ark_id,convert(varchar(10),a.Up_time,120)dt,b.Temperature_Ark-a.Temperature_Ark dfrom tb a inner join tb b on a.Ark_id=b.Ark_id and convert(varchar(10),a.Up_time,120)=convert(varchar(10),b.Up_time,120)where not exists(select 1 from tb where Ark_id=a.Ark_id and convert(varchar(10),Up_time,120)=convert(varchar(10),a.Up_time,120) and Up_time<a.Up_time)and not exists(select 1 from tb where Ark_id=b.Ark_id and convert(varchar(10),Up_time,120)=convert(varchar(10),b.Up_time,120) and Up_time>b.Up_time)/*Ark_id dt d----------- ---------- ---------------------------------------1 2011-11-23 8.072 2011-11-23 12.383 2011-11-23 20.954 2011-11-23 15.345 2011-11-23 15.926 2011-11-23 11.277 2011-11-23 4.328 2011-11-23 1.12(8 行受影响)*/godrop table tb
