34,594
社区成员
发帖
与我相关
我的任务
分享
declare @t table(pid int,tid int,parentid int,layer int,poster varchar(10),posterid int)
insert into @t select 4734182,363580,4734182,0,'user01',34550
insert into @t select 4734192,363580,4734192,1,'user02',40131
insert into @t select 4734195,363580,4734195,1,'user03',22843
insert into @t select 4734198,363580,4734195,2,'user01',34550
insert into @t select 4734203,363580,4734203,1,'user04',38269
select
t.username,t.replyer,count(*) as times
from
(select a.poster as username,b.poster as replyer from @t a,@t b where a.tid=b.tid and a.layer=0 and b.layer=1
union all
select a.poster,b.poster from @t a,@t b where a.tid=b.tid and b.layer>1 and a.pid=b.parentid) t
group by
t.username,t.replyer
order by
t.username,t.replyer
/*
username replyer times
---------- ---------- -----------
user01 user02 1
user01 user03 1
user01 user04 1
user03 user01 1
*/
select t.username,t.replyer,count(*) as times
from (select a.poster as username,b.poster as replyer from tabname a,tabname b where a.tid=b.tid and a.layer=0 and b.layer=1
union all
select a.poster,b.poster from tabname a,tabname b where a.tid=b.tid and b.layer>1 and a.pid=b.parentid) t
group by t.username,t.replyer
请给出表结构,测试数据,相关算法和需要的结果.谢谢!
select
t.username,t.replyer,count(*) as times
from
(select a.poster as username,b.poster as replyer from tabname a,tabname b where a.tid=b.tid and a.layer=0 and b.layer=1
union all
select a.poster,b.poster from tabname a,tabname b where a.tid=b.tid and b.layer>1 and a.pid=b.parentid) t
group by
t.username,t.replyer