求一句sql，根据日期区间遍历

Question

A表
ID StartDate EndDate
1 2011-3-10 2011-3-16
B表
ID AID
1 1
2 1 C表
ID BID EffectDate
1 1 2011-3-10
2 1 2011-3-10
3 1 2011-3-10
4 1 2011-3-11
5 2 2011-3-11
6 2 2011-3-16
7 1 2011-3-11
8 1 2011-3-14
9 2 2011-3-11
10 2 2011-3-12
所需查询结果
BID 2011-3-10 2011-3-11 2011-3-12 2011-3-13 2011-3-14 2011-3-15 2011-3-16
1 3  2  0  0  1  0  0 
2 0  2  1  0  0  0  1 
多谢

Answer 1

格式有点乱
简单的说就是取出B表ID在A表的日期区间内所有日期的总数

Answer 2

把表A弄成如下格式就简单多了，或者建立个日历履历表也可以啊，根据表A求出时间段区间，在行转列
ID Date 
1 2011-3-10 
2 2011-3-11
3 2011-3-12
4 2011-3-13
5 2011-3-14
6 2011-3-15
6 2011-3-16

Answer 3

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,1create 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 操作消除了空值。

Answer 4

建立一个时间表与你的表 LEFT JOIN

Answer 5

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