表中日期字段(不连续)怎样求出相邻两个记录的日期之间的差值

Question

数据如下　　　　　日期　　　　　　　　　　　　　　差值
2007-03-26 00:00:00.000　　　　　　　　　１
2007-03-27 00:00:00.000　　　　　　　　　９
2007-04-05 00:00:00.000　　　　　　　　　１　　　　　　　　
2007-04-06 00:00:00.000

Answer 1

create table tb(日期 datetime)
insert into tb values('2007-03-26 00:00:00.000')
insert into tb values('2007-03-27 00:00:00.000')
insert into tb values('2007-04-05 00:00:00.000')
insert into tb values('2007-04-06 00:00:00.000')
goselect 日期 , datediff(day , 日期 , (select min(日期) from tb where 日期 > t.日期)) 日期2 from tb tdrop table tb/*
日期                                                     日期2         
------------------------------------------------------ ----------- 
2007-03-26 00:00:00.000                                1
2007-03-27 00:00:00.000                                9
2007-04-05 00:00:00.000                                1
2007-04-06 00:00:00.000                                NULL（所影响的行数为 4 行）*/

Answer 2

DECLARE @t TABLE (Dt Datetime)
INSERT INTO @t 
SELECT '2007-03-26 00:00:00.000' UNION ALL SELECT 
'2007-03-27 00:00:00.000' UNION ALL SELECT 
'2007-04-05 00:00:00.000'UNION ALL SELECT 
'2007-04-06 00:00:00.000'SELECT IDENTITY(INT,1,1) as Row,* INTO #tmp FROM @t SELECT Dt,(SELECT DateDiff(day,dt,a.dt) FROM #tmp WHERE row=a.row-1) as Diff
FROM #tmp a
DROP TABLE #tmp

Answer 3

create table tb(日期 datetime)
insert into tb values('2007-03-26 00:00:00.000')
insert into tb values('2007-03-27 00:00:00.000')
insert into tb values('2007-04-05 00:00:00.000')
insert into tb values('2007-04-06 00:00:00.000')
goselect 日期 , datediff(day , 日期 , (select min(日期) from tb where 日期 > t.日期)) 日期2 from tb tdrop table tb/*
日期                                                           差值         
------------------------------------------------------ ----------- 
2007-03-26 00:00:00.000                                1
2007-03-27 00:00:00.000                                9
2007-04-05 00:00:00.000                                1
2007-04-06 00:00:00.000                                NULL（所影响的行数为 4 行）*/