22,206
社区成员
发帖
与我相关
我的任务
分享
select
bid ,
max(case when convert(varchar(10),date,120)='2011-03-10' then [count] end) as [2011-03-10] ,
max(case when convert(varchar(10),date,120)='2011-03-11' then [count] end) as [2011-03-11] ,
max(case when convert(varchar(10),date,120)='2011-03-12' then [count] end) as [2011-03-12] ,
max(case when convert(varchar(10),date,120)='2011-03-13' then [count] end) as [2011-03-13] ,
max(case when convert(varchar(10),date,120)='2011-03-14' then [count] end) as [2011-03-14] ,
max(case when convert(varchar(10),date,120)='2011-03-15' then [count] end) as [2011-03-15] ,
max(case when convert(varchar(10),date,120)='2011-03-16' then [count] end) as [2011-03-16]
from
(select dateadd(dd,number,b.StartDate) as date,c.bid,
(select count(1) from c where bid=c.bid and EffectDate=dateadd(dd,number,a.StartDate)) as [count]
from
master..spt_values t ,b,c
where
type='p'
and
dateadd(dd,number,a.StartDate)<=a.EndDate and a.id=b.aid and b.id=c.bid
group by
dateadd(dd,number,a.StartDate),c.bid) a
group by bid
create table tba(id int,StartDate datetime,EndDate datetime)
insert into tba select 1,'2011-3-10','2011-3-16'
create table tbb(id int,aid int)
insert into tbb select 1,1 union select 2,1
create table tbc(id int,bid int,EffectDate datetime)
insert into tbc
select 1,1,'2011-3-10' union all
select 2,1,'2011-3-10' union all
select 3,1,'2011-3-10' union all
select 4,1,'2011-3-11' union all
select 5,2,'2011-3-11' union all
select 6,2,'2011-3-16' union all
select 7,1,'2011-3-11' union all
select 8,1,'2011-3-14' union all
select 9,2,'2011-3-11' union all
select 10,2,'2011-3-12'
declare @str varchar(3000)
set @str=''
select @str= @str+' ,max(case when convert(varchar(10),date,120)='''+
convert(varchar(10),dateadd(dd,number,b.StartDate),120)+''' then [count] end) as ['+
convert(varchar(10),dateadd(dd,number,b.StartDate),120)+']'
from master..spt_values a,tba b
where type='p' and dateadd(dd,number,b.StartDate)<=b.EndDate
set @str='select bid'+@str+' from (select dateadd(dd,number,b.StartDate) as date,d.bid,
[count]=(select count(1) from tbc where bid=d.bid and EffectDate=dateadd(dd,number,b.StartDate))
from master..spt_values a,tba b,tbb c,tbc d
where type=''p'' and dateadd(dd,number,b.StartDate)<=b.EndDate
and b.id=c.aid and c.id=d.bid
group by dateadd(dd,number,b.StartDate),d.bid) a group by bid'
--print @str
exec(@str)
/*
bid 2011-03-10 2011-03-11 2011-03-12 2011-03-13 2011-03-14 2011-03-15 2011-03-16
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 2 0 0 1 0 0
2 0 2 1 0 0 0 1
警告: 聚合或其他 SET 操作消除了空值。