不妨设表分别为a,b
<一>使用“full join”
select isnull(c.a,d.a),isnull(c.b,d.b),isnull(c.c,d.c),c.d,d.c,d.d
from c
full join
d
on c.a=d.a
and c.b=d.b
and c.c=d.c
<二>不使用“full join”
declare @a as varchar(20)
declare @b as varchar(20)
if (select count(*) from c)>(select count(*) from d)
begin
set @a='c'
set @b='d'
end
else
begin
set @a='d'
set @b='c'
end
exec('
select a.a,a.b,a.c,a.d,b.c,b.d
from '+@a+' a
left join
'+@b+' b
on a.a=b.a
and a.b=b.b
and a.c=b.c
')
select isnull(a.col1,b.col1) as col1,isnull(a.col2,b.col2) as col2,
a.col3,a.col4,b.col3,b.col4
from 表1 a full join 表2 b
on a.col1=b.col1 and a.col2 =b.col2 and a.col3 =b.col3
insert into @t1
select 'aa','bb','rmb','232'
union
select 'aa','bb','uk','231'
union
select 'aa','bb','hk','231'
union
select 'aa','bb','doc','231'
union
select 'aa','bb','eur','231'
union
select 'aa','bb','ge','231'
insert into @t2
select 'aa','bb','rmb','232'
union
select 'aa','bb','uk','231'
union
select 'aa','bb','hk','231'
union
select 'aa','bb','doc','231'
union
select 'aa','bb','eur','231'
union
select 'aa','bb','kor','231'
select t1.a,t1.b,t1.c,t1.d,t2.c,t2.d from @t1 t1
left join @t2 t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c
union
select t2.a,t2.b,t2.c,t2.d,t1.c,t1.d from @t2 t2
left join @t1 t1 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c
不用full join可以用left join加子查询呀。两个表结构一样吧?那就当你的列名是a,b,c,d
select a,b,c,d,e,f from (select * from 表一 left join (select a aa,b bb,c e,d f from 表二) as 表三 on 表一.a=表二.aa and 表一.b=表二.bb) as 表四
--也可这么写
select id,col1,col2,sum(col3) as col3,sum(col4) as col4,sum(col5) as col5,sum(col3b) as col3b,sum(col4b) as col4b,sum(col5b) as col5b
from (
select (select count(*) from #t1 where col1=a.col1 and col2=a.col2 and id<=a.id) as id,col1,col2,col3,col4,col5,cast(null as int) as col3b,cast(null as int) as col4b,cast(null as int) as col5b
from #t1 a
union all
select (select count(*) from #t2 where col1=b.col1 and col2=b.col2 and id<=b.id) as id,col1,col2,null,null,null,col3,col4,col5
from #t2 b
) as t
group by id,col1,col2
select isnull(a.col1,b.col1) as col1,isnull(a.col2,b.col2) as col2,
isnull(a.col3) as col3,isnull(a.col4) as col4,isnull(a.col5) as col5,
isnull(b.col3) as col3b,isnull(b.col4) as col4b,isnull(b.col5) as col5b
from #t1 a full join #t2 b
on a.col1=b.col1 and a.col2 =b.col2
and (select count(*) from #t1 where col1=a.col1 and col2=a.col2 and id<=a.id)=
(select count(*) from #t2 where col1=b.col1 and col2=b.col2 and id<=b.id)