现在有表A,记录了不同人在不同的时间段内(共12个小时)进行的活动 人(P) 开始时间(T1) 结束时间(T2) 活动类型(S)
A 1 4 a
A 5 8 b
A 9 12 c
B 1 5 c
B 6 9 b
B 10 12 a
C 1 3 b
C 4 10 c
C 11 12 d 怎样的查询才能得到下面按时间和活动类型次数得到的表B(即统计每个小时参与活动a,b,c,d的人数) 时间(T) 活动类型a 活动类型b 活动类型c 活动类型d
1 1 1 1 0
2 1 1 1 0
3 1 1 1 0
4 1 0 2 0
5 0 1 2 0
6 0 2 1 0
7 0 2 1 0
8 0 2 1 0
9 0 1 2 0
10 1 0 2 0
11 1 0 1 1
12 1 0 1 1 之前我尝试用笨办法分开几步来计算,但因为数据有点多,处理起来比较麻烦,能不能一步就得到表B呢,请高手帮帮忙啊,先谢啦
A 1 4 a
A 5 8 b
A 9 12 c
B 1 5 c
B 6 9 b
B 10 12 a
C 1 3 b
C 4 10 c
C 11 12 d 怎样的查询才能得到下面按时间和活动类型次数得到的表B(即统计每个小时参与活动a,b,c,d的人数) 时间(T) 活动类型a 活动类型b 活动类型c 活动类型d
1 1 1 1 0
2 1 1 1 0
3 1 1 1 0
4 1 0 2 0
5 0 1 2 0
6 0 2 1 0
7 0 2 1 0
8 0 2 1 0
9 0 1 2 0
10 1 0 2 0
11 1 0 1 1
12 1 0 1 1 之前我尝试用笨办法分开几步来计算,但因为数据有点多,处理起来比较麻烦,能不能一步就得到表B呢,请高手帮帮忙啊,先谢啦
if OBJECT_ID('A') is not null drop table A
create table A(FName nvarchar(20),FBegin Int,FEnd Int,FActionType nvarchar(10))
insert into A(FName,Fbegin,FEnd,FActionType)
select 'A',1,4,'a' union all
select 'A',5,8,'b' union all
select 'A',9,12,'c' union all
select 'B',1,5,'c' union all
select 'B',6,9,'b' union all
select 'B',10,12,'a' union all
select 'C',1,3,'b' union all
select 'C',4,10,'c' union all
select 'C',11,12,'d' if OBJECT_ID('tempdb..#Temp') is not null drop table #Temp
create table #Temp(FMonth int)
insert into #Temp(FMonth)
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12
--计算每个时间段内每个活动参与的人数
select t1.FMonth,t2.FActionType,COUNT(distinct t2.FName) as FSum
into #Temp_Res
from #Temp t1 left join A t2 on t1.FMonth >= t2.FBegin and t1.FMonth <= t2.FEnd
group by t1.FMonth,t2.FActionType
--行转列显示
select v.FMonth
,'活动类型a'=ISNULL((select Fsum from #Temp_Res where FMonth=v.FMonth and FActionType='a'),0)
,'活动类型b'=ISNULL((select Fsum from #Temp_Res where FMonth=v.FMonth and FActionType='b'),0)
,'活动类型c'=ISNULL((select Fsum from #Temp_Res where FMonth=v.FMonth and FActionType='c'),0)
,'活动类型d'=ISNULL((select Fsum from #Temp_Res where FMonth=v.FMonth and FActionType='d'),0)
from #Temp v left join #Temp_Res u on v.FMonth=u.FMonth
group by v.FMonthdrop table #Temp
drop table #Temp_Res
if OBJECT_ID('A') is not null drop table A
create table A(FName nvarchar(20),FBegin Int,FEnd Int,FActionType nvarchar(10))
insert into A(FName,Fbegin,FEnd,FActionType)
select 'A',1,4,'a' union all
select 'A',5,8,'b' union all
select 'A',9,12,'c' union all
select 'B',1,5,'c' union all
select 'B',6,9,'b' union all
select 'B',10,12,'a' union all
select 'C',1,3,'b' union all
select 'C',4,10,'c' union all
select 'C',11,12,'d'
--创建存储过程
create proc wsp
as
declare @star int,@end int
select @star=min(fbegin),@end=max(fbegin) from
(select fbegin from a
union all
select fend from a)b
create table #(n int)
while(@star<=@end)
begin
insert into # select @star
set @star=@star+1
end
declare @sql varchar(8000)
set @sql='select b.n [时间(T)]'
select @sql=@sql+',[活动类型'+FActionType+']=sum(case FActionType when '''+FActionType+''' then 1 else 0 end)'
from (select distinct FActionType from a)b
set @sql=@sql+' from # b left join a on b.n between a.fbegin and fend group by b.n'
exec(@sql)
--调用存储过程
exec wsp
as
begin
declare @i int,
@a int,
@b int,
@c int,
@d int
set @i = 1
create table #a
(t int,
a int,
b int,
c int,
d int)
while(@i< =12)
begin
select @a = count(*) from a where FActionType = 'a' and fbegin <=@i and fend >=@i
select @b = count(*) from a where FActionType = 'b' and fbegin <=@i and fend >=@i
select @c = count(*) from a where FActionType = 'c' and fbegin <=@i and fend >=@i
select @d = count(*) from a where FActionType = 'd' and fbegin <=@i and fend >=@i
insert into #a select @i,@a,@b,@c,@d
set @i = @i +1
end
select * from #a
end
create table A(FName nvarchar(20),FBegin Int,FEnd Int,FActionType nvarchar(10))
insert into A(FName,Fbegin,FEnd,FActionType)
select 'A',1,4,'a' union all
select 'A',5,8,'b' union all
select 'A',9,12,'c' union all
select 'B',1,5,'c' union all
select 'B',6,9,'b' union all
select 'B',10,12,'a' union all
select 'C',1,3,'b' union all
select 'C',4,10,'c' union all
select 'C',11,12,'d' if OBJECT_ID('tempdb..#Temp') is not null drop table Temp
create table Temp(FMonth int)
insert into Temp(FMonth)
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12
declare @sql varchar(1000)
set @sql='select fmonth+fbegin-1 as fmonth'
select @sql=@sql+',sum(case when factiontype='''+factiontype+''' then 1 else 0 end ) as '+factiontype
from (select distinct factiontype from a) aset @sql=@sql+' from a , temp b
where a.fbegin +b.fmonth<=a.fend+1 group by fmonth+fbegin-1'
exec(@sql)fmonth a b c d
----------- ----------- ----------- ----------- -----------
1 1 1 1 0
2 1 1 1 0
3 1 1 1 0
4 1 0 2 0
5 0 1 2 0
6 0 2 1 0
7 0 2 1 0
8 0 2 1 0
9 0 1 2 0
10 1 0 2 0
11 1 0 1 1
12 1 0 1 1(12 行受影响)