select isnull(a.id,b.id)id, a, b from ta a full join tb b on a.id=b.id order by isnull(a.id,b.id)
--> 测试数据: #ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta (id int,a int) insert into #ta select 1,1 union all select 2,1 union all select 3,1 --> 测试数据: #tB if object_id('tempdb.dbo.#tB') is not null drop table #tB go create table #tB (id int,b int) insert into #tB select 2,2 union all select 3,2 union all select 4,2select id=isnull(a.id,b.id), a, b from #ta a full join #tb b on a.id=b.id
id a b ----------- ----------- ----------- 1 1 NULL 2 1 2 3 1 2 4 NULL 2
select A.id,a,b from A full join B on A.id=B.id
create table tbA(id int,a int) insert into tbA select 1,1 insert into tbA select 2,1 insert into tbA select 3,1 create table tbB(id int,b int) insert into tbB select 2,2 insert into tbB select 3,2 insert into tbB select 4,2 go select a.id,b.a,c.b from ( select id from tba union select id from tbb )a left join tba b on a.id=b.id left join tbb c on a.id=c.id go drop table tba,tbb /* id a b ----------- ----------- ----------- 1 1 NULL 2 1 2 3 1 2 4 NULL 2(4 行受影响) */
select case when t1.iid is null then t2.iid else t1.iid end,t1.ia,t2.ib from t1 full join t2 on t1.iid=t2.iid
select case when table1.id is null then table2.id else table1.id end, table1.a,table2.b from table1 full join table2 on table1.id=table2.id 5楼是正解
select case when table1.id is null then table2.id else table1.id end, table1.a,table2.b from table1 full join table2 on table1.id=table2.id
a,
b
from ta a full join tb b
on a.id=b.id
order by isnull(a.id,b.id)
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (id int,a int)
insert into #ta
select 1,1 union all
select 2,1 union all
select 3,1
--> 测试数据: #tB
if object_id('tempdb.dbo.#tB') is not null drop table #tB
go
create table #tB (id int,b int)
insert into #tB
select 2,2 union all
select 3,2 union all
select 4,2select id=isnull(a.id,b.id), a, b
from #ta a
full join #tb b
on a.id=b.id
id a b
----------- ----------- -----------
1 1 NULL
2 1 2
3 1 2
4 NULL 2
insert into tbA select 1,1
insert into tbA select 2,1
insert into tbA select 3,1
create table tbB(id int,b int)
insert into tbB select 2,2
insert into tbB select 3,2
insert into tbB select 4,2
go
select a.id,b.a,c.b
from (
select id from tba union select id from tbb
)a left join tba b on a.id=b.id left join tbb c on a.id=c.id
go
drop table tba,tbb
/*
id a b
----------- ----------- -----------
1 1 NULL
2 1 2
3 1 2
4 NULL 2(4 行受影响)
*/
select
case when table1.id is null then table2.id else table1.id end,
table1.a,table2.b
from table1 full join table2 on table1.id=table2.id
5楼是正解
select
case when table1.id is null then table2.id else table1.id end,
table1.a,table2.b
from table1 full join table2 on table1.id=table2.id