Re: [SQL ] 算出每筆日期間隔
其實是可以做到的,
概念是用分組排序,來找出與自己日期相差最少的,即是自己的"下一筆"。
手邊沒有MSSQL,憑空寫不確定有沒有錯,請試試看:
SELECT Date1 [Date], Orders, daydiff NextDataDayDiff
FROM
(
SELECT Date1, Orders, daydiff
, Row_Number() OVER (PARTITION BY Date1 ORDER BY daydiff) RW
FROM
(
SELECT t1.Date Date1, t2.Date Date2, t1.Orders
, datediff(day,t1.Date ,t2.Date) AS daydiff
FROM @Temp t1 with(nolock)
LEFT JOIN @Temp t2 with(nolock)
ON t1.Date<t2.Date
) temp
) temp
WHERE RW=1
※ 引述《Severine (賽非茵)》之銘言:
: 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), 來自: 118.166.90.109
※ 文章網址: http://www.ptt.cc/bbs/Database/M.1405103824.A.F00.html
→
07/12 21:27, , 1F
07/12 21:27, 1F
→
07/12 21:27, , 2F
07/12 21:27, 2F
推
07/13 01:09, , 3F
07/13 01:09, 3F
→
07/14 01:55, , 4F
07/14 01:55, 4F
→
07/14 01:59, , 5F
07/14 01:59, 5F
推
07/14 20:45, , 6F
07/14 20:45, 6F
→
07/14 23:34, , 7F
07/14 23:34, 7F
→
07/14 23:35, , 8F
07/14 23:35, 8F
推
07/15 12:05, , 9F
07/15 12:05, 9F
→
07/15 12:06, , 10F
07/15 12:06, 10F
→
07/15 12:06, , 11F
07/15 12:06, 11F
→
07/15 12:06, , 12F
07/15 12:06, 12F
推
07/15 12:11, , 13F
07/15 12:11, 13F
→
07/15 23:43, , 14F
07/15 23:43, 14F
→
07/15 23:44, , 15F
07/15 23:44, 15F
推
07/15 23:45, , 16F
07/15 23:45, 16F
→
07/15 23:46, , 17F
07/15 23:46, 17F
推
07/16 00:57, , 18F
07/16 00:57, 18F
→
07/16 00:57, , 19F
07/16 00:57, 19F
→
07/16 00:57, , 20F
07/16 00:57, 20F
→
07/16 00:58, , 21F
07/16 00:58, 21F
→
07/16 00:58, , 22F
07/16 00:58, 22F
→
07/16 00:58, , 23F
07/16 00:58, 23F
→
07/17 14:33, , 24F
07/17 14:33, 24F
→
07/17 14:33, , 25F
07/17 14:33, 25F
→
07/17 14:38, , 26F
07/17 14:38, 26F
→
07/17 14:39, , 27F
07/17 14:39, 27F
討論串 (同標題文章)
Database 近期熱門文章
PTT數位生活區 即時熱門文章