34,575
社区成员
发帖
与我相关
我的任务
分享
;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 行受影响)
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
*/
select 姓名,sum(datediff(dd,入院日期,出院日期)) from tb group by 姓名
select 姓名sum(datediff(dd,入院日期,出院日期)) from tb group by 姓名