求一SQL语句~~~~~~~~~~~~~~~~~~~~~~~
表1
A   1
B   2
C   3
表2
A   3
B   2
D   4

要得到
A     1       3
B     2       2
C     3
D             4

------解决方案--------------------
create table a
(
col1 varchar(5),
col2 varchar(5)
)
create table b
(
col1 varchar(5),
col2 varchar(5)
)
insert into a
select 'A ', '1 ' UNION ALL
select 'B ', '2 ' UNION ALL
select 'C ', '3 '
insert into b
select 'A ', '3 ' UNION ALL
select 'B ', '2 ' UNION ALL
select 'D ', '4 '
GO
select c.col1,isnull(a.col2, ' ') as acol2,isnull(b.col2, ' ') as bcol2
from
(select a.col1 from a union
select b.col1 from b) c
left join a on a.col1=c.col1
left join b on b.col1=c.col1
--结果
col1 acol2 bcol2
----- ----- -----
A 1 3
B 2 2
C 3
D 4
------解决方案--------------------
select (case when a.a is null then b.a else a.a end) a,a.b,b.b
from 表1 a full join 表2 b on a.a=b.a order by a
------解决方案--------------------
刚才复杂了
select isnull(a.a,b.a) a,a.b,b.b from 表1 a full join 表2 b on a.a=b.a order by a
------解决方案--------------------
declare @a table(col1 varchar(3),col2 int)
insert into @a
select 'A ',1
union select 'B ',2
union select 'C ',3

declare @b table(col1 varchar(3),col3 int)
insert into @b
select 'A ',3
union select 'B ',2
union select 'D ',4
select isnull(A.col1,B.col1) as col1,A.col2,B.col3 from @a a full join @b b on a.col1=b.col1
------解决方案--------------------
Select C.[1],A.[2],B.[2]
From (
Select [1] From A union (
Select B.[1] From B Where B.[1] not in (Select [1] From A))) C
Left join (Select [1],[2] From A) A on C.[1]=A.[1]
Left join (Select [1],[2] From B) B on C.[1]=B.[1]