[SQL ] 算出每筆日期間隔
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
→
07/07 22:41, , 1F
07/07 22:41, 1F
→
07/07 22:42, , 2F
07/07 22:42, 2F
推
07/07 22:46, , 3F
07/07 22:46, 3F
→
07/07 22:48, , 4F
07/07 22:48, 4F
→
07/07 22:49, , 5F
07/07 22:49, 5F
→
07/07 23:10, , 6F
07/07 23:10, 6F
→
07/07 23:11, , 7F
07/07 23:11, 7F
討論串 (同標題文章)
Database 近期熱門文章
PTT數位生活區 即時熱門文章
