作者Severine (赛非茵)
看板Database
标题[SQL ] 算出每笔日期间隔
时间Mon Jul 7 17:30:57 2014
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://webptt.com/cn.aspx?n=bbs/Database/M.1404725459.A.328.html
1F：→ scpisces:DATEADD(d,1,T2.Date)=>日期会变为 2/2,2/13,2/14,2/25 07/07 22:41
2F：→ scpisces:只能算出2/13那一笔 07/07 22:42
3F：推 scpisces:我的作法是给每一笔资料流水号 07/07 22:46
4F：→ scpisces:也就是再多一个栏位Seq 07/07 22:48
5F：→ scpisces:然後再用 LEFT JOIN @Temp AS T2 ON T1.Seq = T2.Seq+1 07/07 22:49
6F：→ Severine:我研究看看 为了这个问题苦恼了一整天..谢谢! 07/07 23:10
7F：→ Severine:现在想想流水号的概念不错 我真笨居然没想到T.T 07/07 23:11