Sql查询上下行差值问题

[yingziming]

查询出 同一个车次的 下站与上站的 时间差（下站到站时间减上站发车时间）；表结构如下

站名 到站时间 发车时间 车次
济南 17:57 17:57 T177
泰山 18:43 18:45 T177
兖州 19:52 19:56 T177
邹城 20:09 20:11 T177
枣庄 20:50 20:54 T177
杭州 23:35 23:35 T177
杭州 18:43 18:43 T178
上海南 20:14 20:23 T178
昆山 21:04 21:08 T178
苏州 21:32 21:36 T178
无锡 22:04 22:08 T178

查询的结果如下
站名 到站时间 发车时间 车次 时间间距
济南 17:57 17:57 T177 0
泰山 18:43 18:45 T177 46
兖州 19:52 19:56 T177 53
邹城 20:09 20:11 T177 13
枣庄 20:50 20:54 T177 21
杭州 23:35 23:35 T177 2:41
杭州 18:43 18:43 T178 0
上海南 20:14 20:23 T178 1：31
昆山 21:04 21:08 T178 41
苏州 21:32 21:36 T178 24
无锡 22:04 22:08 T178 28

请教下 sql 语句怎么写 谢谢

[spiritofdragon]

--必须有“站次”字段而且数据得是连续的自然数（否则，查询是还要加上row_number），不能只靠时间排序，不然过夜车怎么办？？
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND type in (N'U'))
DROP TABLE [dbo].[t]
GO
CREATE TABLE [dbo].[t](
[站次] [int] NULL,
[站名] [nvarchar](50) NULL,
[到站时间] [nvarchar](50) NULL,
[发车时间] [nvarchar](50) NULL,
[车次] [nvarchar](50) NULL
)
GO
insert into t
select 1,'济南','17:57','17:57','T177'
union all select 2,'泰山','18:43','18:45','T177'
union all select 3,'兖州','19:52','19:56','T177'
union all select 4,'邹城','20:09','20:11','T177'
union all select 5,'枣庄','20:50','20:54','T177'
union all select 6,'杭州','23:35','23:35','T177'
union all select 1,'杭州','18:43','18:43','T178'
union all select 2,'上海南','20:14','20:23','T178'
union all select 3,'昆山','21:04','21:08','T178'
union all select 4,'苏州','21:32','21:36','T178'
union all select 5,'无锡','22:04','22:08','T178'
union all select 6,'济南','00:20','00:20','T178'--这条是我加的
go
select *
,isnull((
select right(convert(nvarchar(16),convert(datetime,t1.到站时间)- convert(datetime,t2.发车时间),120),5) from t t2 where t1.车次=t2.车次 and t2.站次+1=t1.站次
),'0') 时间间距
from t t1

[开着拖拉机泡妞]

----------------------------------------------------------------
-- Author  :TravyLee
-- Date    :2012-11-02 14:58:35
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
go 
create table [test](
[站名] varchar(6),
[到站时间] time,
[发车时间] time,
[车次] varchar(4)
)
insert [test]
select '济南','17:57','17:57','T177' union all
select '泰山','18:43','18:45','T177' union all
select '兖州','19:52','19:56','T177' union all
select '邹城','20:09','20:11','T177' union all
select '枣庄','20:50','20:54','T177' union all
select '杭州','23:35','23:35','T177' union all
select '杭州','18:43','18:43','T178' union all
select '上海南','20:14','20:23','T178' union all
select '昆山','21:04','21:08','T178' union all
select '苏州','21:32','21:36','T178' union all
select '无锡','22:04','22:08','T178'
go


--这个查询需要注意一个细节  那就是隔天的情况  做过长途火车的都知道吧
with t
as(
select 
	px=ROW_NUMBER()over(order by (select 1)),
	站名,
	dateadd(mi,datepart(hh,到站时间)*60+DATEPART(MI,到站时间),'2012-01-01 00:00:00.000') as 到站时间,
	dateadd(mi,datepart(hh,发车时间)*60+DATEPART(MI,发车时间),'2012-01-01 00:00:00.000') as 发车时间,
	车次
from 
	test
)
select
	a.站名,
	c.到站时间,
	c.发车时间,
	a.车次,
	时间间距=isnull((select DATEDIFF(MI,b.发车时间,a.到站时间) from t b where a.车次=b.车次 and b.px=a.px-1),0)
from 
	t a
inner join
	[test] c
on 
	a.车次=c.车次 and a.站名=c.站名


/*
站名     到站时间             发车时间             车次   时间间距
------ ---------------- ---------------- ---- ------------------------
济南     17:57:00.0000000 17:57:00.0000000 T177 0
泰山     18:43:00.0000000 18:45:00.0000000 T177 46
兖州     19:52:00.0000000 19:56:00.0000000 T177 67
邹城     20:09:00.0000000 20:11:00.0000000 T177 13
枣庄     20:50:00.0000000 20:54:00.0000000 T177 39
杭州     23:35:00.0000000 23:35:00.0000000 T177 161
杭州     18:43:00.0000000 18:43:00.0000000 T178 0
上海南    20:14:00.0000000 20:23:00.0000000 T178 91
昆山     21:04:00.0000000 21:08:00.0000000 T178 41
苏州     21:32:00.0000000 21:36:00.0000000 T178 24
无锡     22:04:00.0000000 22:08:00.0000000 T178 28

(11 行受影响)


*/