表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][email protected]_id
group by good_name, [user_id]) A
full join
(select good_serial,[user_id],count(*) as [tb2_count] from tb2
where [user_id][email protected]_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][email protected]_id
group by good_name, [user_id]) A
full join
(select good_serial,[user_id],count(*) as [tb2_count] from tb2
where [user_id][email protected]_id
group by good_serial,[user_id]) B