根据类型、时间求一列数据的差解决方案
根据类型、时间求一列数据的差
Ark_ID int 4
Up_Time datetime
Temperature_Ark numeric 9
Ark_ID Up_Time Temperature_Ark
1 2011-11-23 7:50:02 258452.25
2 2011-11-23 7:50:02 201406.7
3 2011-11-23 7:50:02 257207.78
4 2011-11-23 7:50:02 262221.55
5 2011-11-23 7:50:02 377021.81
6 2011-11-23 7:50:02 150993.8
7 2011-11-23 7:50:02 4060757.56
8 2011-11-23 7:50:02 45649.9
1 2011-11-23 8:00:02 258452.77
2 2011-11-23 8:00:02 201408.04
3 2011-11-23 8:00:02 257209.57
4 2011-11-23 8:00:02 262222.54
5 2011-11-23 8:00:02 377022.72
6 2011-11-23 8:00:02 150994.81
7 2011-11-23 8:00:02 4060758.16
8 2011-11-23 8:00:02 45650.02
1 2011-11-23 8:10:02 258453.55
2 2011-11-23 8:10:02 201409.22
3 2011-11-23 8:10:02 257211.97
4 2011-11-23 8:10:02 262223.86
5 2011-11-23 8:10:02 377023.89
6 2011-11-23 8:10:02 150996.19
7 2011-11-23 8:10:02 4060758.73
8 2011-11-23 8:10:02 45650.1
1 2011-11-23 8:20:02 258454.12
2 2011-11-23 8:20:02 201410.65
3 2011-11-23 8:20:02 257214.86
4 2011-11-23 8:20:02 262225.7
5 2011-11-23 8:20:02 377025.42
6 2011-11-23 8:20:02 150997.86
7 2011-11-23 8:20:02 4060759.13
8 2011-11-23 8:20:02 45650.22
1 2011-11-23 8:30:02 258454.85
2 2011-11-23 8:30:02 201412.42
3 2011-11-23 8:30:02 257217.63
4 2011-11-23 8:30:02 262227.86
5 2011-11-23 8:30:02 377027.19
6 2011-11-23 8:30:02 150999.21
7 2011-11-23 8:30:02 4060759.64
8 2011-11-23 8:30:02 45650.35
1 2011-11-23 8:40:02 258455.94
2 2011-11-23 8:40:02 201414.1
3 2011-11-23 8:40:02 257220.36
4 2011-11-23 8:40:02 262230.37
5 2011-11-23 8:40:02 377029.9
6 2011-11-23 8:40:02 151000.57
7 2011-11-23 8:40:02 4060760.18
8 2011-11-23 8:40:02 45650.61
1 2011-11-23 8:50:02 258457.18
2 2011-11-23 8:50:02 201415.6
3 2011-11-23 8:50:02 257223.05
4 2011-11-23 8:50:02 262232.65
5 2011-11-23 8:50:02 377032.48
6 2011-11-23 8:50:02 151002.01
7 2011-11-23 8:50:02 4060760.75
8 2011-11-23 8:50:02 45650.72
1 2011-11-23 9:00:02 258458.99
2 2011-11-23 9:00:02 201417.48
3 2011-11-23 9:00:02 257225.87
4 2011-11-23 9:00:02 262234.79
5 2011-11-23 9:00:02 377035.13
6 2011-11-23 9:00:02 151003.65
7 2011-11-23 9:00:02 4060761.32
8 2011-11-23 9:00:02 45650.86
1 2011-11-23 9:10:02 258460.32
2 2011-11-23 9:10:02 201419.08
3 2011-11-23 9:10:02 257228.73
4 2011-11-23 9:10:02 262236.89
5 2011-11-23 9:10:02 377037.73
6 2011-11-23 9:10:02 151005.07
7 2011-11-23 9:10:02 4060761.88
8 2011-11-23 9:10:02 45651.02
根据Ark_ID、Up_Time这二个变量计算Temperature_Ark差值,根据8个类型Ark_ID每一天计算一次Temperature_Ark,只要计算这一天的最后一条数据减去一天最开始的数据,求那个好人帮忙
------解决方案--------------------
Ark_ID int 4
Up_Time datetime
Temperature_Ark numeric 9
Ark_ID Up_Time Temperature_Ark
1 2011-11-23 7:50:02 258452.25
2 2011-11-23 7:50:02 201406.7
3 2011-11-23 7:50:02 257207.78
4 2011-11-23 7:50:02 262221.55
5 2011-11-23 7:50:02 377021.81
6 2011-11-23 7:50:02 150993.8
7 2011-11-23 7:50:02 4060757.56
8 2011-11-23 7:50:02 45649.9
1 2011-11-23 8:00:02 258452.77
2 2011-11-23 8:00:02 201408.04
3 2011-11-23 8:00:02 257209.57
4 2011-11-23 8:00:02 262222.54
5 2011-11-23 8:00:02 377022.72
6 2011-11-23 8:00:02 150994.81
7 2011-11-23 8:00:02 4060758.16
8 2011-11-23 8:00:02 45650.02
1 2011-11-23 8:10:02 258453.55
2 2011-11-23 8:10:02 201409.22
3 2011-11-23 8:10:02 257211.97
4 2011-11-23 8:10:02 262223.86
5 2011-11-23 8:10:02 377023.89
6 2011-11-23 8:10:02 150996.19
7 2011-11-23 8:10:02 4060758.73
8 2011-11-23 8:10:02 45650.1
1 2011-11-23 8:20:02 258454.12
2 2011-11-23 8:20:02 201410.65
3 2011-11-23 8:20:02 257214.86
4 2011-11-23 8:20:02 262225.7
5 2011-11-23 8:20:02 377025.42
6 2011-11-23 8:20:02 150997.86
7 2011-11-23 8:20:02 4060759.13
8 2011-11-23 8:20:02 45650.22
1 2011-11-23 8:30:02 258454.85
2 2011-11-23 8:30:02 201412.42
3 2011-11-23 8:30:02 257217.63
4 2011-11-23 8:30:02 262227.86
5 2011-11-23 8:30:02 377027.19
6 2011-11-23 8:30:02 150999.21
7 2011-11-23 8:30:02 4060759.64
8 2011-11-23 8:30:02 45650.35
1 2011-11-23 8:40:02 258455.94
2 2011-11-23 8:40:02 201414.1
3 2011-11-23 8:40:02 257220.36
4 2011-11-23 8:40:02 262230.37
5 2011-11-23 8:40:02 377029.9
6 2011-11-23 8:40:02 151000.57
7 2011-11-23 8:40:02 4060760.18
8 2011-11-23 8:40:02 45650.61
1 2011-11-23 8:50:02 258457.18
2 2011-11-23 8:50:02 201415.6
3 2011-11-23 8:50:02 257223.05
4 2011-11-23 8:50:02 262232.65
5 2011-11-23 8:50:02 377032.48
6 2011-11-23 8:50:02 151002.01
7 2011-11-23 8:50:02 4060760.75
8 2011-11-23 8:50:02 45650.72
1 2011-11-23 9:00:02 258458.99
2 2011-11-23 9:00:02 201417.48
3 2011-11-23 9:00:02 257225.87
4 2011-11-23 9:00:02 262234.79
5 2011-11-23 9:00:02 377035.13
6 2011-11-23 9:00:02 151003.65
7 2011-11-23 9:00:02 4060761.32
8 2011-11-23 9:00:02 45650.86
1 2011-11-23 9:10:02 258460.32
2 2011-11-23 9:10:02 201419.08
3 2011-11-23 9:10:02 257228.73
4 2011-11-23 9:10:02 262236.89
5 2011-11-23 9:10:02 377037.73
6 2011-11-23 9:10:02 151005.07
7 2011-11-23 9:10:02 4060761.88
8 2011-11-23 9:10:02 45651.02
根据Ark_ID、Up_Time这二个变量计算Temperature_Ark差值,根据8个类型Ark_ID每一天计算一次Temperature_Ark,只要计算这一天的最后一条数据减去一天最开始的数据,求那个好人帮忙
------解决方案--------------------
- SQL code
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 all select 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) ) k group by Ark_ID, convert(varchar(10),Up_Time,120)
------解决方案--------------------
- SQL code
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.25 insert into tb select 2,'2011-11-23 7:50:02',201406.7 insert into tb select 3,'2011-11-23 7:50:02',257207.78 insert into tb select 4,'2011-11-23 7:50:02',262221.55 insert into tb select 5,'2011-11-23 7:50:02',377021.81 insert into tb select 6,'2011-11-23 7:50:02',150993.8 insert into tb select 7,'2011-11-23 7:50:02',4060757.56 insert into tb select 8,'2011-11-23 7:50:02',45649.9 insert into tb select 1,'2011-11-23 8:00:02',258452.77 insert into tb select 2,'2011-11-23 8:00:02',201408.04 insert into tb select 3,'2011-11-23 8:00:02',257209.57 insert into tb select 4,'2011-11-23 8:00:02',262222.54 insert into tb select 5,'2011-11-23 8:00:02',377022.72 insert into tb select 6,'2011-11-23 8:00:02',150994.81 insert into tb select 7,'2011-11-23 8:00:02',4060758.16 insert into tb select 8,'2011-11-23 8:00:02',45650.02 insert into tb select 1,'2011-11-23 8:10:02',258453.55 insert into tb select 2,'2011-11-23 8:10:02',201409.22 insert into tb select 3,'2011-11-23 8:10:02',257211.97 insert into tb select 4,'2011-11-23 8:10:02',262223.86 insert into tb select 5,'2011-11-23 8:10:02',377023.89 insert into tb select 6,'2011-11-23 8:10:02',150996.19 insert into tb select 7,'2011-11-23 8:10:02',4060758.73 insert into tb select 8,'2011-11-23 8:10:02',45650.1 insert into tb select 1,'2011-11-23 8:20:02',258454.12 insert into tb select 2,'2011-11-23 8:20:02',201410.65 insert into tb select 3,'2011-11-23 8:20:02',257214.86 insert into tb select 4,'2011-11-23 8:20:02',262225.7 insert into tb select 5,'2011-11-23 8:20:02',377025.42 insert into tb select 6,'2011-11-23 8:20:02',150997.86 insert into tb select 7,'2011-11-23 8:20:02',4060759.13 insert into tb select 8,'2011-11-23 8:20:02',45650.22 insert into tb select 1,'2011-11-23 8:30:02',258454.85 insert into tb select 2,'2011-11-23 8:30:02',201412.42 insert into tb select 3,'2011-11-23 8:30:02',257217.63 insert into tb select 4,'2011-11-23 8:30:02',262227.86 insert into tb select 5,'2011-11-23 8:30:02',377027.19 insert into tb select 6,'2011-11-23 8:30:02',150999.21 insert into tb select 7,'2011-11-23 8:30:02',4060759.64 insert into tb select 8,'2011-11-23 8:30:02',45650.35 insert into tb select 1,'2011-11-23 8:40:02',258455.94 insert into tb select 2,'2011-11-23 8:40:02',201414.1 insert into tb select 3,'2011-11-23 8:40:02',257220.36 insert into tb select 4,'2011-11-23 8:40:02',262230.37 insert into tb select 5,'2011-11-23 8:40:02',377029.9 insert into tb select 6,'2011-11-23 8:40:02',151000.57 insert into tb select 7,'2011-11-23 8:40:02',4060760.18 insert into tb select 8,'2011-11-23 8:40:02',45650.61 insert into tb select 1,'2011-11-23 8:50:02',258457.18 insert into tb select 2,'2011-11-23 8:50:02',201415.6 insert into tb select 3,'2011-11-23 8:50:02',257223.05 insert into tb select 4,'2011-11-23 8:50:02',262232.65 insert into tb select 5,'2011-11-23 8:50:02',377032.48 insert into tb select 6,'2011-11-23 8:50:02',151002.01 insert into tb select 7,'2011-11-23 8:50:02',4060760.75 insert into tb select 8,'2011-11-23 8:50:02',45650.72 insert into tb select 1,'2011-11-23 9:00:02',258458.99 insert into tb select 2,'2011-11-23 9:00:02',201417.48 insert into tb select 3,'2011-11-23 9:00:02',257225.87 insert into tb select 4,'2011-11-23 9:00:02',262234.79 insert into tb select 5,'2011-11-23 9:00:02',377035.13 insert into tb select 6,'2011-11-23 9:00:02',151003.65 insert into tb select 7,'2011-11-23 9:00:02',4060761.32 insert into tb select 8,'2011-11-23 9:00:02',45650.86 insert into tb select 1,'2011-11-23 9:10:02',258460.32 insert into tb select 2,'2011-11-23 9:10:02',201419.08 insert into tb select 3,'2011-11-23 9:10:02',257228.73 insert into tb select 4,'2011-11-23 9:10:02',262236.89 insert into tb select 5,'2011-11-23 9:10:02',377037.73 insert into tb select 6,'2011-11-23 9:10:02',151005.07 insert into tb select 7,'2011-11-23 9:10:02',4060761.88 insert into tb select 8,'2011-11-23 9:10:02',45651.02 go select a.Ark_id,convert(varchar(10),a.Up_time,120)dt,b.Temperature_Ark-a.Temperature_Ark d from 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.07 2 2011-11-23 12.38 3 2011-11-23 20.95 4 2011-11-23 15.34 5 2011-11-23 15.92 6 2011-11-23 11.27 7 2011-11-23 4.32 8 2011-11-23 1.12 (8 行受影响) */ go drop table tb