select intime, aa=sum(case when chindex('aa',content)>0 then 1 else 0 end), bb=sum(case when chindex('bb',content)>0 then 1 else 0 end), cc=sum(case when chindex('cc',content)>0 then 1 else 0 end), dd=sum(case when chindex('dd',content)>0 then 1 else 0 end) from tb1 group by intime
/*-----[create]的括号要去掉------*/ [create] table tb1 (intime datetime,[user] varchar(20),content varchar(20))insert tb1 values ('2005-01-01','111','aa,bb,cc,dd') insert tb1 values ('2005-01-01','112','aa,bb') insert tb1 values ('2005-01-01','111','bb') insert tb1 values ('2005-01-02','112','cc,dd') insert tb1 values ('2005-01-02','112','cc') insert tb1 values ('2005-01-02','113','cc') insert tb1 values ('2005-01-02','111','cc,dd') insert tb1 values ('2005-01-03','113','bb,dd')declare @t table(id int identity(1,1),b bit) insert @t select top 200 0 from sysobjects a,sysobjects bselect intime,[user],content=substring(a.content,b.id,charindex(',',a.content+',',b.id)-b.id) into # from tb1 a,@t b where charindex(',',','+a.content,b.id)=b.id and b.id<=len(a.content) select * from # declare @sql varchar(8000) select @sql='select intime' select @sql=@sql+',['+content+']=sum(case when content='''+[content]+''' then 1 else 0 end)' from # group by content select @sql=@sql+' from # group by intime order by intime' exec(@sql) drop table # drop table tb1intime aa bb cc dd ------------------------------------------------------ ----------- ----------- ----------- ----------- 2005-01-01 00:00:00.000 2 3 1 1 2005-01-02 00:00:00.000 0 0 4 2 2005-01-03 00:00:00.000 0 1 0 1
create table tb(intime smalldatetime, [user] varchar(5), content text) insert tb select '2005-01-01','111','aa,bb,cc,dd' union all select '2005-01-01','112','aa,bb' union all select '2005-01-01','111','bb' union all select '2005-01-02','112','cc,dd' union all select '2005-01-02','112','cc' union all select '2005-01-02','113','cc' union all select '2005-01-02','111','cc,dd' union all select '2005-01-03','113','bb,dd'select stime ,aa=count(distinct aa),bb=count(distinct bb), cc=count(distinct cc),dd=count(distinct dd) from ( select stime=intime, aa=(case when charindex('aa',content)>0 then [user] else null end ), bb=(case when charindex('bb',content)>0 then [user] else null end ), cc=(case when charindex('cc',content)>0 then [user] else null end ), dd=(case when charindex('dd',content)>0 then [user] else null end ) from tb)aa group by stime drop table tb
aa=sum(case when chindex('aa',content)>0 then 1 else 0 end),
bb=sum(case when chindex('bb',content)>0 then 1 else 0 end),
cc=sum(case when chindex('cc',content)>0 then 1 else 0 end),
dd=sum(case when chindex('dd',content)>0 then 1 else 0 end)
from tb1
group by intime
[create] table tb1
(intime datetime,[user] varchar(20),content varchar(20))insert tb1 values ('2005-01-01','111','aa,bb,cc,dd')
insert tb1 values ('2005-01-01','112','aa,bb')
insert tb1 values ('2005-01-01','111','bb')
insert tb1 values ('2005-01-02','112','cc,dd')
insert tb1 values ('2005-01-02','112','cc')
insert tb1 values ('2005-01-02','113','cc')
insert tb1 values ('2005-01-02','111','cc,dd')
insert tb1 values ('2005-01-03','113','bb,dd')declare @t table(id int identity(1,1),b bit)
insert @t select top 200 0 from sysobjects a,sysobjects bselect intime,[user],content=substring(a.content,b.id,charindex(',',a.content+',',b.id)-b.id)
into # from tb1 a,@t b
where charindex(',',','+a.content,b.id)=b.id
and b.id<=len(a.content)
select * from #
declare @sql varchar(8000)
select @sql='select intime'
select @sql=@sql+',['+content+']=sum(case when content='''+[content]+''' then 1 else 0 end)'
from # group by content
select @sql=@sql+' from # group by intime order by intime'
exec(@sql)
drop table #
drop table tb1intime aa bb cc dd
------------------------------------------------------ ----------- ----------- ----------- -----------
2005-01-01 00:00:00.000 2 3 1 1
2005-01-02 00:00:00.000 0 0 4 2
2005-01-03 00:00:00.000 0 1 0 1
[user] varchar(5),
content text)
insert tb
select '2005-01-01','111','aa,bb,cc,dd' union all
select '2005-01-01','112','aa,bb' union all
select '2005-01-01','111','bb' union all
select '2005-01-02','112','cc,dd' union all
select '2005-01-02','112','cc' union all
select '2005-01-02','113','cc' union all
select '2005-01-02','111','cc,dd' union all
select '2005-01-03','113','bb,dd'select stime ,aa=count(distinct aa),bb=count(distinct bb),
cc=count(distinct cc),dd=count(distinct dd)
from (
select stime=intime,
aa=(case when charindex('aa',content)>0 then [user] else null end ),
bb=(case when charindex('bb',content)>0 then [user] else null end ),
cc=(case when charindex('cc',content)>0 then [user] else null end ),
dd=(case when charindex('dd',content)>0 then [user] else null end )
from tb)aa group by stime
drop table tb
/*
stime aa bb cc dd
--------------------------------------------------------
2005-01-01 00:00:00 2 2 1 1
2005-01-02 00:00:00 0 0 3 2
2005-01-03 00:00:00 0 1 0 1*/
(intime datetime,[user] varchar(20),content varchar(20))insert tb1 values ('2005-01-01','111','aa,bb,cc,dd')
insert tb1 values ('2005-01-01','112','aa,bb')
insert tb1 values ('2005-01-01','111','bb')
insert tb1 values ('2005-01-02','112','cc,dd')
insert tb1 values ('2005-01-02','112','cc')
insert tb1 values ('2005-01-02','113','cc')
insert tb1 values ('2005-01-02','111','cc,dd')
insert tb1 values ('2005-01-03','113','bb,dd')declare @t table(id int identity(1,1),b bit)
insert @t select top 200 0 from sysobjects a,sysobjects bselect intime,[user],content=substring(a.content,b.id,charindex(',',a.content+',',b.id)-b.id)
into # from tb1 a,@t b
where charindex(',',','+a.content,b.id)=b.id
and b.id<=len(a.content)
group by a.intime,[user],substring(a.content,b.id,charindex(',',a.content+',',b.id)-b.id)select * from #
declare @sql varchar(8000)
select @sql='select intime'
select @sql=@sql+',['+content+']=sum(case when content='''+content+''' then 1 else 0 end)'
from # group by content
select @sql=@sql+' from # group by intime order by intime'
exec(@sql)
drop table #
drop table tb1
intime aa bb cc dd
------------------------------------------------------ ----------- ----------- ----------- -----------
2005-01-01 00:00:00.000 2 2 1 1
2005-01-02 00:00:00.000 0 0 3 2
2005-01-03 00:00:00.000 0 1 0 1