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
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
full join B
on a.matercode1 = b.matercode2
select distinct isnull(a.matercode1,b.matercode2) as matercode1,isnull(maternum1,0) as maternum1,
isnull(maternum2,0) as maternum2
from a full outer join b
on a.matercode1=b.matercode2
drop table A
go
create table A(matercode1 int,maternum1 int)
insert into A(matercode1,maternum1) values(1001, 5)
insert into A(matercode1,maternum1) values(1002, 6)
go
if object_id('pubs..B') is not null
drop table B
go
create table B(matercode2 int,maternum2 int)
insert into B(matercode2,maternum2 ) values(1001, 6)
insert into B(matercode2,maternum2 ) values(1003, 5)
goselect isnull(a.matercode1,b.matercode2) matercode1 , isnull(a.maternum1,0) maternum1,isnull(b.maternum2,0) maternum2 from A
full join B
on a.matercode1 = b.matercode2
order by a.matercode1drop table A,B
/*
matercode1 maternum1 maternum2
----------- ----------- -----------
1001 5 6
1002 6 0
1003 0 5(所影响的行数为 3 行)
*/
1001 5 6
1003 0 5
1002 6 0
union
select a.*,isnull(MaterNum1)as MaterNum1 from B a left join A b ON a.matercode2=b.matercode1