--表结构:
Create table #t
(
id int identity(1,1),
name nvarchar(50),--车辆名称
arrivetime datetime--出发,到达时间
)
--测试数据
insert into #t values('车辆1','2011-4-20 8:00:00')
insert into #t values('车辆1','2011-4-20 8:50:00')
insert into #t values('车辆1','2011-4-20 9:20:00')
insert into #t values('车辆2','2011-4-20 8:20:00')
insert into #t values('车辆2','2011-4-20 8:30:00')
insert into #t values('车辆1','2011-4-20 7:30:00')
insert into #t values('车辆1','2011-4-20 10:30:00')
--查询逻辑:按车辆,出发达到达时间列排序,同一辆车两条相邻的数据为出发和达到时间。
--想要的结果
序号 车牌号 出发时间 到达时间
1 车辆1 2011-4-20 7:30:00 2011-4-20 8:00:00
2 车辆1 2011-4-20 8:00:00 2011-4-20 8:50:00
3 车辆1 2011-4-20 8:50:00 2011-4-20 9:20:00
4 车辆1 2011-4-20 9:20:00 2011-4-20 10:30:00
5 车辆2 2011-4-20 8:20:00 2011-4-20 8:30:00
Create table #t
(
id int identity(1,1),
name nvarchar(50),--车辆名称
arrivetime datetime--出发,到达时间
)
--测试数据
insert into #t values('车辆1','2011-4-20 8:00:00')
insert into #t values('车辆1','2011-4-20 8:50:00')
insert into #t values('车辆1','2011-4-20 9:20:00')
insert into #t values('车辆2','2011-4-20 8:20:00')
insert into #t values('车辆2','2011-4-20 8:30:00')
insert into #t values('车辆1','2011-4-20 7:30:00')
insert into #t values('车辆1','2011-4-20 10:30:00')
--查询逻辑:按车辆,出发达到达时间列排序,同一辆车两条相邻的数据为出发和达到时间。
--想要的结果
序号 车牌号 出发时间 到达时间
1 车辆1 2011-4-20 7:30:00 2011-4-20 8:00:00
2 车辆1 2011-4-20 8:00:00 2011-4-20 8:50:00
3 车辆1 2011-4-20 8:50:00 2011-4-20 9:20:00
4 车辆1 2011-4-20 9:20:00 2011-4-20 10:30:00
5 车辆2 2011-4-20 8:20:00 2011-4-20 8:30:00
select
name as 车牌号,
arrivetime as 出发时间,
(select top 1 arrivetime
from #t a
where a.name = #t.name
and a.arrivetime > #t.arrivetime
order by a.arrivetime) as 到达时间
from #t
where exists(
select top 1 arrivetime
from #t a
where a.name = #t.name
and a.arrivetime > #t.arrivetime)
order by name,arrivetime/*
车牌号 出发时间 到达时间
-------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------
车辆1 2011-04-20 07:30:00.000 2011-04-20 08:00:00.000
车辆1 2011-04-20 08:00:00.000 2011-04-20 08:50:00.000
车辆1 2011-04-20 08:50:00.000 2011-04-20 09:20:00.000
车辆1 2011-04-20 09:20:00.000 2011-04-20 10:30:00.000
车辆2 2011-04-20 08:20:00.000 2011-04-20 08:30:00.000(所影响的行数为 5 行)
*/
(SELECT row_number() over(order by name,arrivetime) as lid, * FROM #T )a
left join
(SELECT row_number() over(order by name,arrivetime) as lid, * FROM #T ) b on a.lid= b.lid-1
where a.name = b.name and b.name is not null
Create table #t
(
id int identity(1,1),
name nvarchar(50),--车辆名称
arrivetime datetime--出发,到达时间
)
--测试数据
insert into #t values('车辆1','2011-4-20 8:00:00')
insert into #t values('车辆1','2011-4-20 8:50:00')
insert into #t values('车辆1','2011-4-20 9:20:00')
insert into #t values('车辆2','2011-4-20 8:20:00')
insert into #t values('车辆2','2011-4-20 8:30:00')
insert into #t values('车辆1','2011-4-20 7:30:00')
insert into #t values('车辆1','2011-4-20 10:30:00')select [name] as 车辆,arrivetime as 出发时间 ,
到达时间=(select min(i.arrivetime) from #t as i where i.[name]=t.[name] and i.arrivetime>t.arrivetime)
from #T as t where (select min(i.arrivetime) from #t as i where i.[name]=t.[name] and i.arrivetime>t.arrivetime) is not null order by [name],arrivetime
[name] ,
arrivetime
FROM #t
)
SELECT ROW_NUMBER() OVER ( ORDER BY a.[name], a.arrivetime ) AS 序号 ,
a.[name] AS 车牌号 ,
a.arrivetime AS 出发时间 ,
b.arrivetime AS 到达时间
FROM t a
JOIN t b ON a.id + 1 = b.id
AND a.[name] = b.[name]
/*
序号 车牌号 出发时间 到达时间
-------------------- -------------------------------------------------- ----------------------- -----------------------
1 车辆1 2011-04-20 07:30:00.000 2011-04-20 08:00:00.000
2 车辆1 2011-04-20 08:00:00.000 2011-04-20 08:50:00.000
3 车辆1 2011-04-20 08:50:00.000 2011-04-20 09:20:00.000
4 车辆1 2011-04-20 09:20:00.000 2011-04-20 10:30:00.000
5 车辆2 2011-04-20 08:20:00.000 2011-04-20 08:30:00.000(5 行受影响)
*/
(
id int identity(1,1),
name nvarchar(50),--车辆名称
arrivetime datetime--出发,到达时间
)
--测试数据
insert into #t values('车辆1','2011-4-20 8:00:00')
insert into #t values('车辆1','2011-4-20 8:50:00')
insert into #t values('车辆1','2011-4-20 9:20:00')
insert into #t values('车辆2','2011-4-20 8:20:00')
insert into #t values('车辆2','2011-4-20 8:30:00')
insert into #t values('车辆1','2011-4-20 7:30:00')
insert into #t values('车辆1','2011-4-20 10:30:00')
--SQL:
;with cte as
(
select rowno=ROW_NUMBER() over(partition by [name] order by arrivetime), * from #t
)
select
序号=ROW_NUMBER() over(order by getdate()),
车牌号=a.name,
出发时间=a.arrivetime,
到达时间=b.arrivetime
from cte a inner join cte b
on a.[name] = b.[name] and a.rowno = b.rowno-1
/*
1 车辆1 2011-04-20 07:30:00.000 2011-04-20 08:00:00.000
2 车辆1 2011-04-20 08:00:00.000 2011-04-20 08:50:00.000
3 车辆1 2011-04-20 08:50:00.000 2011-04-20 09:20:00.000
4 车辆1 2011-04-20 09:20:00.000 2011-04-20 10:30:00.000
5 车辆2 2011-04-20 08:20:00.000 2011-04-20 08:30:00.000
*/