跪求.在线等
A表:
matercode1     maternum1    
1001                 5                  
1002                 6                  


B表
matercode2     maternum2    
1001                 6                  
1003                 5                  

如何使查询结果为
matercode1     maternum1       MaterNum2      
1001                 5                           6
1002                 6                           0
1003                 0                           5
也就是说．相同的matercode中的maternum要在一条上显示．如果没有的．则显示为0

------解决方案--------------------
select a.matercode1,isnull(a.maternum1,0),isnull(b.MaterNum2,0) from A表 a inner join B表 b on a.matercode1 =b.matercode2
------解决方案--------------------
select a.matercode1,isnull(a.maternum1,0),isnull(b.MaterNum2,0) from A表 a
FULL JOIN B表 b on a.matercode1 =b.matercode2

要用 FULL JOIN

------解决方案--------------------
A表:
matercode1 maternum1
1001 5
1002 6


B表
matercode2 maternum2
1001 6
1003 5
select a.matercode,isnull(b.maternum1,0) maternum1 ,isnull(c.maternum2,0) maternum2 from
(
select matercode1 as matercode tba union
select matercode2 as matercode from tbb
) a left outer join tba b on a.matercode=b.matercode1 left outer join tbb c on a.matercode=c.matercode2
------解决方案--------------------
create table A表(matercode1 int, maternum1 int )
insert A表
select 1001 , 5
union all select 1002 , 6


create table B表(matercode2 int, maternum2 int )
insert B表
select 1001 , 6
union all select 1003 , 5

select matercode1=a.matercode1,maternum1=isnull(a.maternum1,0),MaterNum2=isnull(b.MaterNum2,0) from A表 a left join B表 b on a.matercode1 =b.matercode2
union
select matercode1=a.matercode2,maternum1=isnull(b.maternum1,0),MaterNum2=isnull(a.MaterNum2,0) from B表 a left join A表 b on a.matercode2 =b.matercode1

drop table A表,B表