列车表(记录号,执行日期,车次,计划到站时间,计划出站时间,关联车次) Train(Rowid, Sch_Date, Train_Num, Sch_Start_Time, Sch_Stall_Time, Train_Rlv) 100000001, 20100101, 3231, 201001011110, 201001011305, 3232 100000002, 20100101, 3232, 2010...
展开
发布于2014-03-03 10:42
最佳答案
select t1.Sch_Stall_Time ,t2.Sch_Start_Time
from Train t1
left join Train t2 on t1.Train_Rlv = t2.Train_Num
/**
declare @Train table (
Rowid nvarchar(100),
Sch_Date char(8),
Train_Num char(4),
Sch_Start_Time char(12),
Sch_Stall_Time char(12),
Train_Rlv char(4)
)
insert into @Train values('100000001', '20100101', '3231', '201001011110', '201001011305', '3232')
insert into @Train values('100000002', '20100101', '3232', '201001011410', '201001011605', '3231')
select t1.Sch_Stall_Time ,t2.Sch_Start_Time
from @Train t1
inner join @Train t2 on t1.Train_Rlv = t2.Train_Num
Sch_Stall_Time Sch_Start_Time
-------------- --------------
201001011305 201001011410
201001011605 201001011110
*/
因为你这里记录1的Train_Rlv 和记录2的Train_Num 相同,记录2的Train_Rlv 和记录1的Train_Num 相同,所以出了两行记录,如果把t2.Train_Rlv 改为其他的值(如3233),结果就是
Sch_Stall_Time Sch_Start_Time
-------------- --------------
201001011305 201001011410