表联合查询

 declare @t table(id int,no int,text1 varchar(50),text2 varchar(50)) insert into @t(id,no,text1,text2) select 101,10,'你好','不好' union all select 102,11,'我好','真好' declare @b table(id int,no int,text3 varchar(50),text4 varchar(50)) insert into @b(id,no,text3,text4) select 101,10,'佛当',null union all select 101,10,'房东',null union all select 102,12,null,'告示' union all select 102,12,null,'高端' union all select 102,12,null,'框架' select * from @t select * from @b select isnull(a.id,b.id) id, isnull(a.no,b.no) no, max(a.text1) text1, max(a.text2) text2, sum(case when b.text3 is not null then 1 else null end) text3, sum(case when b.text4 is not null then 1 else null end)text4 from @t a full join @b b on a.id=b.id and a.no=b.no group by isnull(a.id,b.id),isnull(a.no,b.no)