比较麻烦的SQL
举例:
张三5月1日来入住宿舍A---5月3日转到宿舍B,5月5日又转回宿舍A直到10日离开宿舍A,一共住辽10天
怎样写SQL算出 在宿舍A住了8天,宿舍B住2天,如果中间又有转到宿舍C
记录流入表(类型判断 1表示入住,2表示转 3表示出)
日期 宿舍 转往宿舍 姓名 类型判断
2013-05-01 宿舍A 张三 1
2013-05-03 宿舍A 宿舍B 张三 2
2013-05-05 宿舍B 宿舍A 张三 2
2013-05-10 宿舍A 张三 3
怎样写SQL好
[解决办法]
use Tempdb
go
--> -->
declare @T table([日期] Datetime,[宿舍] nvarchar(3),[转往宿舍] nvarchar(3),[姓名] nvarchar(2),[类型判断] int)
Insert @T
select '2013-05-01',N'宿舍A',null,N'张三',1 union all
select '2013-05-03',N'宿舍A',N'宿舍B',N'张三',2 union all
select '2013-05-05',N'宿舍B',N'宿舍A',N'张三',2 union all
select '2013-05-10',N'宿舍A',null,N'张三',3
declare @Dt datetime
set @Dt='2013-05-10'
Select
sum(case when a.[类型判断]=1 then datediff(dd,a.[日期],isnull(b.[日期],@Dt))+1 else datediff(dd,a.[日期],isnull(b.[日期],@Dt)) end) as [天数]
from @T as a
outer apply(select top 1 * from @T where [日期]>a.[日期] order by [日期] asc) as b
where a.[日期]<='2013-05-10'
select [宿舍],sum([天数]) as [天数]
from
(Select
isnull(b.[宿舍],a.[宿舍]) as [宿舍],case when a.[类型判断]=1 then 1 else 0 end+ datediff(dd,a.[日期],isnull(b.[日期],@Dt)) as [天数]
from @T as a
outer apply(select top 1 * from @T where [日期]>a.[日期] order by [日期] asc) as b
where a.[日期]<='2013-05-10') as T
group by [宿舍]
/*
天数
10
宿舍天数
宿舍A8
宿舍B2
*/
