表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语句应该怎么写??
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语句应该怎么写??
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
GOdeclare @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 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=1good_name tb1_count tb2_count
---------- ----------- -----------
凳子 1 0
桌子 2 2(所影响的行数为 2 行)
(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=2
good_name tb1_count tb2_count
---------- ----------- -----------
桌子 1 0(所影响的行数为 1 行)