求教 两个表关联汇总的问题！
表1:tb1
good_id good_name good_serial user_id
1 桌子 00001 1
1 桌子 00002 1
1 桌子 00003 2
2 凳子 00004 1
表2：tb2
good_id good_serial user_id
1 桌子 1
1 桌子 1
按user_id= '1 '查询，得出结果
good_name tb1_count tb2_count
桌子 2 2
凳子 1 0
按user_id= '2 '查询，得出结果
good_name tb1_count tb2_count
桌子 1 0
请问sql语句应该怎么写？？
[解决办法]
create table tb1(good_id int,good_name varchar(10),good_serial varchar(10),[user_id] int)
insert into tb1
select 1, '桌子 ', '00001 ',1 union all
select 1, '桌子 ', '00002 ',1 union all
select 1, '桌子 ', '00003 ',2 union all
select 2, '登子 ', '00004 ',1
create table tb2(good_id int,good_serial varchar(10),[user_id] int)
insert into tb2
select 1, '桌子 ',1 union all
select 1, '桌子 ',1
GO
declare @user_id int
set @user_id=1
select isnull(A.good_name,B.good_serial) as good_name,isnull(A.tb1_count,0) as tb1_count,isnull(B.tb2_count,0) as tb2_count
from
(select good_name, [user_id],count(*) as [tb1_count] from tb1
where [user_id]=@user_id
group by good_name, [user_id]) A
full join
(select good_serial,[user_id],count(*) as [tb2_count] from tb2
where [user_id]=@user_id
group by good_serial,[user_id]) B
on A.good_name=B.good_serial
/* user_id=1結果:
good_name tb1_count tb2_count
---------- ----------- -----------
桌子 2 2
登子 1 0
*/
set @user_id=2
select isnull(A.good_name,B.good_serial) as good_name,isnull(A.tb1_count,0) as tb1_count,isnull(B.tb2_count,0) as tb2_count
from
(select good_name, [user_id],count(*) as [tb1_count] from tb1
where [user_id]=@user_id
group by good_name, [user_id]) A
full join
(select good_serial,[user_id],count(*) as [tb2_count] from tb2
where [user_id]=@user_id
group by good_serial,[user_id]) B
on A.good_name=B.good_serial
/*user_id=2結果:
good_name tb1_count tb2_count
---------- ----------- -----------
桌子 1 0
*/
drop table tb1,tb2
[解决办法]
select a.good_name,isnull(a.tb1_count,0)as tb1_count,isnull(b.tb2_count,0)as tb2_count from
(select user_id,good_name,count(*)as tb1_count from tb1 group by user_id,good_name)a
left join
(select user_id,good_serial,count(*)as tb2_count from tb2 group by user_id,good_serial)b
on a.user_id=b.user_id and a.good_name=b.good_serial
where a.user_id=1
good_name tb1_count tb2_count
---------- ----------- -----------
凳子 1 0
桌子 2 2
（所影响的行数为 2 行）
