遇到一个难题,不知道怎么查较好,求助!
现有两张表A,B,以A为主表,查询B中DeviceTime与Table1中DeviceTime时间相差最近的记录,相差不超过30秒,则取B中的Speed和Rpm, 如果超过30秒无数据,则Speed取A表,Rpm为0。
两表是针对同一个对象的,直接根据DeviceTime时间差判断即可,实际应用时表明会是动态的,要考虑到sql语句拼接的效率。
AID DeviceTime Speed
1 2012-11-03 02:05:03.000 45
2 2012-11-03 02:05:14.000 36
3 2012-11-03 02:05:24.000 28
4 2012-11-03 02:05:33.000 53
5 2012-11-03 02:05:44.000 80
6 2012-11-03 02:05:54.000 95
7 2012-11-03 02:07:23.000 52
8 2012-11-03 02:07:34.000 28
9 2012-11-03 02:10:13.000 84
10 2012-11-03 02:10:23.000 58
11 2012-11-03 02:10:33.000 28
12 2012-11-03 02:10:43.000 19
13 2012-11-03 02:10:53.000 75 BID DeviceTime Speed Rpm
1 2012-11-03 02:05:10.413 48 2033
2 2012-11-03 02:05:20.000 43 1983
3 2012-11-03 02:05:30.000 30 2344
4 2012-11-03 02:05:40.000 53 5532
5 2012-11-03 02:05:50.000 83 3342
6 2012-11-03 02:07:08.000 63 3450
7 2012-11-03 02:07:18.000 71 2352
8 2012-11-03 02:07:28.000 50 7823
9 2012-11-03 02:10:20.000 82 2341
10 2012-11-03 02:10:30.000 69 2552
11 2012-11-03 02:10:40.000 58 2894
12 2012-11-03 02:10:50.000 92 1143
现有两张表A,B,以A为主表,查询B中DeviceTime与Table1中DeviceTime时间相差最近的记录,相差不超过30秒,则取B中的Speed和Rpm, 如果超过30秒无数据,则Speed取A表,Rpm为0。
两表是针对同一个对象的,直接根据DeviceTime时间差判断即可,实际应用时表明会是动态的,要考虑到sql语句拼接的效率。
AID DeviceTime Speed
1 2012-11-03 02:05:03.000 45
2 2012-11-03 02:05:14.000 36
3 2012-11-03 02:05:24.000 28
4 2012-11-03 02:05:33.000 53
5 2012-11-03 02:05:44.000 80
6 2012-11-03 02:05:54.000 95
7 2012-11-03 02:07:23.000 52
8 2012-11-03 02:07:34.000 28
9 2012-11-03 02:10:13.000 84
10 2012-11-03 02:10:23.000 58
11 2012-11-03 02:10:33.000 28
12 2012-11-03 02:10:43.000 19
13 2012-11-03 02:10:53.000 75 BID DeviceTime Speed Rpm
1 2012-11-03 02:05:10.413 48 2033
2 2012-11-03 02:05:20.000 43 1983
3 2012-11-03 02:05:30.000 30 2344
4 2012-11-03 02:05:40.000 53 5532
5 2012-11-03 02:05:50.000 83 3342
6 2012-11-03 02:07:08.000 63 3450
7 2012-11-03 02:07:18.000 71 2352
8 2012-11-03 02:07:28.000 50 7823
9 2012-11-03 02:10:20.000 82 2341
10 2012-11-03 02:10:30.000 69 2552
11 2012-11-03 02:10:40.000 58 2894
12 2012-11-03 02:10:50.000 92 1143
case when [ss]<30 then Rpm else 0 end
from A cross apply(select top 1 [ss]=abs(datediff(ss,a.DeviceTime,DeviceTime)),*
from B order by 1) t