跪求.在线等

[qiankun19851231]

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

[leo_lesley]

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

[hrb2008]

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

[肥胖的柠檬]

a.matercode1 改成 isnull(a.matercode1,a.matercode2),

[air_eijil]

left outer join 就好了~

[leo_lesley]

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表

[clarkchiang]

--创建测试a表
create table a
(
matercode1 varchar(255),
maternum1 int
)
go

--创建测试b表
create table b
(
matercode2 varchar(255),
maternum2 int
)
go

--插入测试数据
insert into a values('1001',5)
go
insert into a values('1002',6)
go
insert into b values('1001',6)
go
insert into b values('1003',5)
go


--按楼主的要求显示的SQL语句
select a.matercode1,isnull(a.maternum1,0) maternum1 ,isnull(b.maternum2,0) maternum2 from a left join b on a.matercode1 = b.matercode2
union
select b.matercode2,isnull(a.maternum1,0),isnull(b.maternum2,0) from a right join b on a.matercode1 = b.matercode2


--测试结果
matercode1 maternum1 maternum2
1001 5 6
1002 6 0
1003 0 5

[leo_lesley]

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

[fa_ge]

select a.matercode1,isnull(a.maternum1,0)as maternum1,isnull(b.maternum2,0)as maternum2
from A a LEFT JOIN B b on a.matercode1=b.matercode2
union
select a.matercode2,isnull(b.maternum1,0)as maternum1,isnull(a.maternum2,0)as maternum2
from A a left join B b ON a.a.matercode2=b.matercode1