更新距离：
update Train_Number set Distance=t.Distance
from Train_Number,(select TrainNum,max(cast(Distance as int)) as Distance from Train_NumDetail
group by TrainNum)as t
WHERE Train_Number.TrainNum=t.TrainNum
更新运行时间：
create PROCEDURE [dbo].[P_SetTrainNumDetailRunTime]
AS
BEGIN
declare @StartTime nvarchar(50)
declare @TrainNum nvarchar(50)
declare @RowCount int
declare @RowIndex int
declare @Train_Number table
(
RowNo int identity(1,1) not null,
TrainNum nvarchar(50),
StartTime nvarchar(50)
)
declare @ArriveTime nvarchar(50)
declare @Detail_RowCount int
declare @Detail_RowIndex int
declare @RunTime int--运行分钟数
declare @RunTimeStr nvarchar(50)--运行时间
declare @Hour int --小时
declare @Minute int --分钟
declare @PreStationRunTime int -- 上一站的运行时间
declare @MinuteOneDay int --一天多少分钟
select @PreStationRunTime=0
select @MinuteOneDay=1440--一天1440分钟
insert into @Train_Number select TrainNum,StartTime from Train_Number order by id
select @RowCount=count(0) from @Train_Number
select @RowIndex=1
while(@RowIndex<=@RowCount)
begin
select @TrainNum=TrainNum,@StartTime=StartTime from @Train_Number where RowNo=@RowIndex
print @TrainNum
select @Detail_RowCount=count(0) from Train_NumDetail where TrainNum=@TrainNum
select @Detail_RowIndex=1
select @PreStationRunTime=0--上一站的运行时间清0
while(@Detail_RowIndex<=@Detail_RowCount)
begin
select @ArriveTime=ArriveTime from Train_NumDetail where TrainNum=@TrainNum and StationNum=@Detail_RowIndex
select @RunTime=datediff(mi,convert(datetime,@StartTime),convert(datetime,@ArriveTime))
while(@RunTime<0 or @RunTime<@PreStationRunTime)
begin
select @RunTime=@RunTime+@MinuteOneDay
end
--修改RunTime字段，奖分钟转化为几小时几分钟
select @RunTimeStr=''
select @Hour=@RunTime/60
select @Minute=@RunTime%60
if(@Hour>0)
begin
select @RunTimeStr=convert(varchar(10),@Hour)+'小时'
end
if(@Minute>0)
begin
select @RunTimeStr=@RunTimeStr + convert(varchar(10),@Minute)+'分'
end
update Train_NumDetail set RunTime=@RunTimeStr where TrainNum=@TrainNum and StationNum=@Detail_RowIndex
--把本站运行的分钟数赋给@PreStationRunTime
select @PreStationRunTime=@RunTime
select @Detail_RowIndex=@Detail_RowIndex+1
end
select @RowIndex=@RowIndex+1
end
END
