表A结构如下:
userid item visittime
1 ab 01:00
1 ab 01:20
1 ac 02:00
2 ab 01:02
2 ac 01:04表C存放了需要统计的item,结构
item
ab
ac
bc我要对这张表里面的数据进行统计,计算每个userid对于存在于C中的item的访问次数,如下
userid item1 item1_num item2 item2_num....
1 ab 2 ac 1
2 ab 1 ac 1
怎么做才能得到这样的结果,而且效率不至于太低?
userid item visittime
1 ab 01:00
1 ab 01:20
1 ac 02:00
2 ab 01:02
2 ac 01:04表C存放了需要统计的item,结构
item
ab
ac
bc我要对这张表里面的数据进行统计,计算每个userid对于存在于C中的item的访问次数,如下
userid item1 item1_num item2 item2_num....
1 ab 2 ac 1
2 ab 1 ac 1
怎么做才能得到这样的结果,而且效率不至于太低?
insert into A values(1,'ab','01:00')
insert into A values(1,'ab','01:20')
insert into A values(1,'ac','02:00')
insert into A values(2,'ab','01:02')
insert into A values(2,'ac','01:04') create table C(item varchar(10))
insert into C values('ab')
insert into C values('ac')
insert into C values('bc')
declare @i int,@sql varchar(8000)select @i=0,@sql=''select @i=@i+1,@sql=@sql+',[item'+rtrim(@i)+']='''+item+''''+
',[item'+rtrim(@i)+'_num]=sum(case item when '''+item+''' then 1 else 0 end)'
from Cset @sql='select userid'+@sql+' from A group by userid order by userid'exec(@sql)
/*
userid item1 item1_num item2 item2_num item3 item3_num
----------- ----- ----------- ----- ----------- ----- -----------
1 ab 2 ac 1 bc 0
2 ab 1 ac 1 bc 0
*/drop table A,C
insert into tb values(1, 'ab', '01:00')
insert into tb values(1, 'ab', '01:20')
insert into tb values(1, 'ac', '02:00')
insert into tb values(2, 'ab', '01:02')
insert into tb values(2, 'ac', '01:04')
godeclare @sql varchar(8000)
set @sql = 'select userid'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then item else '' '' end) [item' + cast(px as varchar) + ']'
+ ' , sum(case px when ''' + cast(px as varchar) + ''' then 1 else 0 end) [item' + cast(px as varchar) + '_num]'
from (select distinct px from (select px=(select COUNT(DISTINCT item) from tb where userid=a.userid and item<a.item)+1 , * from tb a) t ) as a
set @sql = @sql + ' from (select px=(select COUNT(DISTINCT item) from tb where userid=a.userid and item<a.item)+1 , * from tb a) t group by userid'
exec(@sql) drop table tb/*
userid item1 item1_num item2 item2_num
----------- ---------- ----------- ---------- -----------
1 ab 2 ac 1
2 ab 1 ac 1
*/
我忘了一个问题,如果我要在最后一列列出不在C中的item被访问的次数,又该怎么做呢?