DB: MS SQL
估狗了很久 刚刚也在本版搜寻了一下类似问题 找不太到..
只好发文问一下!
我想要取得每笔纪录的时间间隔 请问该怎么做呢?
=========================================================
问完以后改写了一个blueshop的范例
DECLARE @Temp table (Date datetime, Orders int)
INSERT INTO @Temp VALUES ('2012/2/1',1000)
INSERT INTO @Temp VALUES ('2012/2/2',800)
INSERT INTO @Temp VALUES ('2012/2/3',3500)
INSERT INTO @Temp VALUES ('2012/2/4',4000)
SELECT
T1.Date ,
ISNULL(datediff(day,T2.Date ,T1.Date),null) AS daydiff
FROM @Temp AS T1
LEFT JOIN @Temp AS T2 ON T1.Date = DATEADD(d,1,T2.Date)
===============以上可以算出后四笔的相差时间==============
但是以下就不行,想必是对left join用法的理解错误
DECLARE @Temp table (Date datetime, Orders int)
INSERT INTO @Temp VALUES ('2012/2/1',1000)
INSERT INTO @Temp VALUES ('2012/2/12',800) <=修改过
INSERT INTO @Temp VALUES ('2012/2/13',3500) <=
INSERT INTO @Temp VALUES ('2012/2/24',4000) <=
SELECT
T1.Date ,
ISNULL(datediff(day,T2.Date ,T1.Date),null) AS daydiff
FROM @Temp AS T1
LEFT JOIN @Temp AS T2 ON T1.Date = DATEADD(d,1,T2.Date)
========================================================
请问该如何修改呢? 谢谢 ><

DATEADD(d,1,T2.Date)=>日期会变为 2/2,2/13,2/14,2/25只能算出2/13那一笔我的作法是给每一笔资料流水号也就是再多一个字段Seq然后再用 LEFT JOIN @Temp AS T2 ON T1.Seq = T2.Seq+1

我研究看看 为了这个问题苦恼了一整天..谢谢!现在想想流水号的概念不错 我真笨居然没想到T.T