2个表,
表A
a1 a2
1 9
2 10
3 67
4 33
表B
b1 b2
2 31
4 99
想实现这样
a1 a2 b2
1 9 NULL(或0或不体现任何数)
2 10 31
3 67 NULL(或0或不体现任何数)
4 33 99
谢谢各位指导~
CREATE TABLE #a ([a1] [char] (10),[a2] [int])
CREATE TABLE #b ([b1] [char] (10),[b2] [int])insert into #a(a1,a2) values('1',9)
insert into #a(a1,a2) values('2',10)
insert into #a(a1,a2) values('3',67)
insert into #a(a1,a2) values('4',33) insert into #b(b1,b2) values('2',31)
insert into #b(b1,b2) values('4',99) --显示NULL
--select isnull(#a.a1,#b.b1) a1,#a.a2 a2,#b.b2 b2
--from #a full join #b on #a.a1=#b.b1--显示0
select #A.a1, #A.a2, isnull(#B.b2, 0)
from #A left join #B on #A.a1 = #B.b1drop table #a
drop table #b
表A
a1 a2
1 9
2 10
3 67
4 33
表B
b1 b2
2 31
4 99
想实现这样
a1 a2 b2
1 9 NULL(或0或不体现任何数)
2 10 31
3 67 NULL(或0或不体现任何数)
4 33 99
谢谢各位指导~
CREATE TABLE #a ([a1] [char] (10),[a2] [int])
CREATE TABLE #b ([b1] [char] (10),[b2] [int])insert into #a(a1,a2) values('1',9)
insert into #a(a1,a2) values('2',10)
insert into #a(a1,a2) values('3',67)
insert into #a(a1,a2) values('4',33) insert into #b(b1,b2) values('2',31)
insert into #b(b1,b2) values('4',99) --显示NULL
--select isnull(#a.a1,#b.b1) a1,#a.a2 a2,#b.b2 b2
--from #a full join #b on #a.a1=#b.b1--显示0
select #A.a1, #A.a2, isnull(#B.b2, 0)
from #A left join #B on #A.a1 = #B.b1drop table #a
drop table #b
from a full join b on a.bid=b.bid
drop table A
go
if object_id('pubs..B') is not null
drop table B
gocreate table A(
aid varchar(10),
a1 varchar(10),
a2 varchar(10)
)create table B(
bid varchar(10),
b1 varchar(10),
b2 varchar(10)
)insert into A(aid,a1,a2) values('1','A','A')
insert into A(aid,a1,a2) values('2','B','B')
insert into A(aid,a1,a2) values('3','C','C')insert into B(bid,b1,b2) values('1','B','B')
insert into B(bid,b1,b2) values('2','C','C')select isnull(a.aid,b.bid) aid,a.a1,a.a2,b.b1,b.b2
from a full join b on a.aid=b.biddrop table A
drop table Baid a1 a2 b1 b2
---------- ---------- ---------- ---------- ----------
1 A A B B
2 B B C C
3 C C NULL NULL