作者felaray (傲娇鱼)
看板Database
标题Re: [SQL ] 算出每笔日期间隔
时间Fri Jul 18 11:17:13 2014
create Table
#Test (SN int, MyDay Date,Class int)
Insert into
#test Values(
1,'2014/01/01',1),(
2,'2014/01/02',2),(
3,'2014/01/07',1),(
4,'2014/01/14',1),(
5,'2014/01/24',3),(
6,'2014/01/25',3),(
7,'2014/01/26',1),(
8,'2014/01/27',2)
;With
cte as
(
Select *,ROW_NUMBER()Over(PARTITION by class order by Myday asc) Rn
From
#test Where
class=2
)
,
Cte1
as
(
Select SN,Myday,Class,NULL as datedif,Rn From
cte Where rn=1
Union All
Select B.SN,B.Myday,B.Class,DATEDIFF(day,A.Myday,b.Myday) ,B.Rn
From
cte1 A
Inner join
cte B on A.Rn = B.Rn-1
)
Select SN,Myday,Class,datedif From
cte1
Drop table
#test
==========================================================
SQL 2012以後的比较简单
CREATE TABLE #Temp (SN INT, MyDay DATE, Class INT)
INSERT INTO #Temp
VALUES (1,'1/1/2014',1),
(1,'1/2/2014',2),
(3,'1/7/2014',1),
(4,'1/14/2014',1),
(5,'1/24/2014',3)
SELECT *,
DATEDIFF(D,
LAG(MyDay) OVER (ORDER BY SN),MyDay) AS Diff
FROM #Temp
WHERE Class = 1
DROP TABLE #Temp
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 124.10.80.50
※ 文章网址: http://webptt.com/cn.aspx?n=bbs/Database/M.1405653436.A.1C5.html