INSERT INTO c SELECT a.bh, a.boy, b.girl FROM a FULL OUTER JOIN b ON a.bh = b.bh
上面的语句有个问题,bh这列只能显示到3 啊,下面都是null
select bh , boy , NULL as gril union all select bh , NULL as boy , gril order by 1
if OBJECT_ID('Tempdb..#a') is not null drop table #a if OBJECT_ID('Tempdb..#b') is not null drop table #b create table #a( bh int null, boy nvarchar(10) null ) create table #b( bh int null, girl nvarchar(10) null ) insert into #a select 1,'aa' union all select 2,'bb' union all select 3,'cc' insert into #b select 2,'dd' union all select 4,'ee' union all select 5,'ff' SELECT isnull(a.bh,b.bh) bh, isnull(a.boy,'') boy, isnull(b.girl,'') girl FROM #a a FULL OUTER JOIN #b b ON a.bh = b.bh--------------------- --结果(3 行受影响)(3 行受影响) bh boy girl ----------- ---------- ---------- 1 aa 2 bb dd 3 cc 4 ee 5 ff(5 行受影响)
SELECT a.bh, a.boy, b.girl
FROM a
FULL OUTER JOIN b ON a.bh = b.bh
select bh , boy , NULL as gril
union all
select bh , NULL as boy , gril
order by 1
if OBJECT_ID('Tempdb..#a') is not null drop table #a
if OBJECT_ID('Tempdb..#b') is not null drop table #b
create table #a(
bh int null,
boy nvarchar(10) null
)
create table #b(
bh int null,
girl nvarchar(10) null
)
insert into #a
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'
insert into #b
select 2,'dd' union all
select 4,'ee' union all
select 5,'ff'
SELECT isnull(a.bh,b.bh) bh, isnull(a.boy,'') boy, isnull(b.girl,'') girl
FROM #a a
FULL OUTER JOIN #b b ON a.bh = b.bh---------------------
--结果(3 行受影响)(3 行受影响)
bh boy girl
----------- ---------- ----------
1 aa
2 bb dd
3 cc
4 ee
5 ff(5 行受影响)