医院同一病人多次住院，记录了每次住院的时间，我现在想求一个差数列，算出其每次住院的间隔天数
示例 如下
姓名 入院日期 出院日期
aaaaaa 20140101 20140115
aaaaaa 20140215 20140230
bbbbbb 20140201 20140216
bbbbbb 20140218 20140301
bbbbbb 20140305 20140319
希望得到的结果是
姓名 入院间隔
aaaaaa 31
bbbbbb 2
bbbbbb 4
------解决思路----------------------
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(6),[入院日期] Datetime,[出院日期] DATETIME)
Insert #T
select N'aaaaaa','20140101','20140115' union all
select N'aaaaaa','20140215','20140228' union all
select N'bbbbbb','20140201','20140216' union all
select N'bbbbbb','20140218','20140301' union all
select N'bbbbbb','20140305','20140319'
Go
SELECT a.[姓名],DATEDIFF(dd,a.[出院日期],b.[入院日期]) AS [间隔天数]
FROM #T AS a
INNER JOIN #T AS b ON a.[姓名] = b.[姓名]
AND a.[入院日期] < b.[入院日期]
WHERE b.入院日期 = ( SELECT MIN([入院日期])
FROM #T
WHERE [姓名] = b.[姓名]
AND [入院日期] > a.[入院日期]
)
/*
姓名 间隔天数
aaaaaa 31
bbbbbb 2
bbbbbb 4
*/
------解决思路----------------------
;with cte(userid,startdate,enddate) as(
select 'aaaaaa','20140101','20140115' union all
select 'aaaaaa','20140215','20140230' union all
select 'bbbbbb','20140201','20140216' union all
select 'bbbbbb','20140218','20140301' union all
select 'bbbbbb','20140305','20140319'
),
cteWork as(
select userid,startdate,enddate,row_number() over(partition by userid order by startdate,enddate) as sn
from cte
)
select a.userid,datediff(day,a.enddate,b.startdate) as cnt
from cteWork as a
join cteWork as b on a.userid=b.userid and b.sn-a.sn=1
userid cnt
------ -----------
aaaaaa 31
bbbbbb 2
bbbbbb 4
(3 行受影响)