carNum driverId inTime inDistance outTime outDistance
---------- ---------- ------------------------------ -------------------- ----------------
4454 A001 2006-06-19 09:00:04 352003195 2006-06-19 09:18:04 352003195
4454 A005 2006-06-19 09:16:54 352003195 2006-06-19 10:00:25 352003195
4454 A005 2006-06-19 10:05:35 352003195 2006-06-19 10:58:41 352004605
闽K7954 1 2006-06-19 15:10:08 0 2006-06-19 15:11:34 0
闽K7954 1 2006-06-19 15:13:57 0 2006-06-19 15:15:02 0
闽K7954 A001 2006-06-19 09:16:12 0 2006-06-19 09:16:58 0
闽K7954 A004 NULL NULL 2006-06-19 09:17:49 0我要实现的是对inTime为NULL的记录的inTime,inDistance字段赋值,具体如下。
其中,@beginTime为已经赋值的变量。请看以下语句,这是我现在使用的语句:
update a set
inTime=
(
select top 1 gpsTime from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
),
inDistance=
(
select top 1 distanceDiff from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
)
from @driverTable a where a.inTime is null
我要实现的就是gpsTime,distanceDiff两个字段的值分别对对应记录的inTime,inDistance字段赋值。在Oracle中可以通过大致如下方式实现:
update a set
(inTime,inDistance)=
(
select top 1 gpsTime, distanceDiff from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
)
from @driverTable a where a.inTime is null
但是在SQL Server中无法这样实现。请各位指教,如何简化update语句。谢谢!
---------- ---------- ------------------------------ -------------------- ----------------
4454 A001 2006-06-19 09:00:04 352003195 2006-06-19 09:18:04 352003195
4454 A005 2006-06-19 09:16:54 352003195 2006-06-19 10:00:25 352003195
4454 A005 2006-06-19 10:05:35 352003195 2006-06-19 10:58:41 352004605
闽K7954 1 2006-06-19 15:10:08 0 2006-06-19 15:11:34 0
闽K7954 1 2006-06-19 15:13:57 0 2006-06-19 15:15:02 0
闽K7954 A001 2006-06-19 09:16:12 0 2006-06-19 09:16:58 0
闽K7954 A004 NULL NULL 2006-06-19 09:17:49 0我要实现的是对inTime为NULL的记录的inTime,inDistance字段赋值,具体如下。
其中,@beginTime为已经赋值的变量。请看以下语句,这是我现在使用的语句:
update a set
inTime=
(
select top 1 gpsTime from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
),
inDistance=
(
select top 1 distanceDiff from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
)
from @driverTable a where a.inTime is null
我要实现的就是gpsTime,distanceDiff两个字段的值分别对对应记录的inTime,inDistance字段赋值。在Oracle中可以通过大致如下方式实现:
update a set
(inTime,inDistance)=
(
select top 1 gpsTime, distanceDiff from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
)
from @driverTable a where a.inTime is null
但是在SQL Server中无法这样实现。请各位指教,如何简化update语句。谢谢!
set inTime = gpsTime,
inDistance = distanceDiff
where intime is null
set
inTime =b.gpsTime,
inDistance=b.distanceDiff
from
@driverTable a,
gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
and
a.inTime is null
inTime=
(
select top 1 gpsTime from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
),
inDistance=
(
select top 1 distanceDiff from gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
order by b.gpsTime
)
from @driverTable a where a.inTime is null
set
inTime =b.gpsTime,
inDistance=b.distanceDiff
from
@driverTable a,
gpsReceRealTime b
where
b.telephone=a.simNum and b.gpsTime >= @beginTime and b.gpsTime < a.outTime
and
not exists(select 1 from gpsReceRealTime where telephone=a.simNum and gpsTime>=@beginTime and gpsTime<a.outTime and gpsTime>b.gpsTime)
and
a.inTime is null
我要实现的是对inTime为NULL的记录的inTime,inDistance字段赋值,具体如下。
其中,@beginTime为已经赋值的变量。
update @driverTable
set inDistances=@beginTime
where inTime is null
汗,你把它想得太过于简单了吧。这样的话我就不上来问了。。
不过还是谢谢啊。
问题一直没解决,没有贵人相助。。