火车更新

更新距离：

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