[SQL ] 算出每筆日期間隔

看板Database作者Severine (賽非茵)時間9年前 (2014/07/07 17:30)推噓1(1推 0噓 6→)

留言7則, 2人參與討論串1/5 (看更多)

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) ======================================================== 請問該如何修改呢? 謝謝 >< -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 59.120.142.132 ※ 文章網址: http://www.ptt.cc/bbs/Database/M.1404725459.A.328.html

→

scpisces

07/07 22:41, , 1^F

07/07 22:41, 1^F

→

scpisces

07/07 22:42, , 2^F

07/07 22:42, 2^F

推

scpisces

07/07 22:46, , 3^F

07/07 22:46, 3^F