上下行相减问题

[lzyx119]

各位，问题如下：

表 A 如下：

id s_time e_time

101 8:00 9:30

230 10:23 12:15

365 13:30 14:09

125 14:20 16:50

62 17:00 20:20


需要得到的结果：(用下一行的s_time减去上一行的e_time得到时间差（分钟））

id s_time e_time 时间差(分钟)

101 8:00 9:30 0

230 10:23 12:15 53

365 13:30 14:09 75

125 14:20 16:50 11

62 17:00 20:20 10


请问怎样用sql语句实现？

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-18 11:40:10
-- 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 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
	DROP TABLE [a]
GO
CREATE TABLE [a] ([id] [int],[s_time] VARCHAR(10),[e_time] VARCHAR(10))
INSERT INTO [a]
SELECT '101','8:00','9:30' UNION ALL
SELECT '230','10:23','12:15' UNION ALL
SELECT '365','13:30','14:09' UNION ALL
SELECT '125','14:20','16:50' UNION ALL
SELECT '62','17:00','20:20'


-->SQL查询如下:

;WITH T AS
(
	SELECT RN=row_number()OVER(ORDER BY GETDATE()),*
	FROM A
)
SELECT A.id,A.s_time,A.e_time,isnull(DATEDIFF(MI,B.e_time,A.s_time),0) [时间差(分钟)]
FROM T A
	LEFT JOIN T B 
		ON A.RN-1=B.RN
/*
id          s_time     e_time     时间差(分钟)
----------- ---------- ---------- -----------
101         8:00       9:30       0
230         10:23      12:15      53
365         13:30      14:09      75
125         14:20      16:50      11
62          17:00      20:20      10

(5 行受影响)
*/

[dawugui]

create table A(id int, s_time varchar(10), e_time varchar(10))
insert into a values(101 , '08:00', '09:30') 
insert into a values(230 , '10:23', '12:15') 
insert into a values(365 , '13:30', '14:09') 
insert into a values(125 , '14:20', '16:50') 
insert into a values(62  , '17:00', '20:20')
go

select t.* , 
       [时间差(分钟)] = isnull(datediff(mi, '2000-01-01 ' +(select top 1 e_time from a where s_time < t.s_time order by s_time desc),'2000-01-01 ' + s_time),0)
from a t


drop table a  

/*
id          s_time     e_time     时间差(分钟)     
----------- ---------- ---------- ----------- 
101         08:00      09:30      0
230         10:23      12:15      53
365         13:30      14:09      75
125         14:20      16:50      11
62          17:00      20:20      10

（所影响的行数为 5 行）


*/