A.1 A.2 A.3
001 a b
002 c d
003 e f
B.1 B.2 B.3
003 g h
004 i j变成
C.1 C.2 C.3 C.4 C.5
001 a b
002 c d
003 e f g h
004 i j表A,B如上,要合并成表C,麻烦高手给出SQL语句。
001 a b
002 c d
003 e f
B.1 B.2 B.3
003 g h
004 i j变成
C.1 C.2 C.3 C.4 C.5
001 a b
002 c d
003 e f g h
004 i j表A,B如上,要合并成表C,麻烦高手给出SQL语句。
from ta a full join tb b
on a.col1=b.col1
select a.A.1 as C.1,a.A.2 as C.2,a.A.3 as C.3,b.B.2 as C.4,b.B.3 as C.5
from A a
left join B b on a.A.1=b.B.1
2楼也不能让A.1,B.1放到C.1
drop table aa
if object_id('bb') is not null
drop table bb
create table aa(a1 varchar(3),a2 varchar(1),a3 varchar(1))
create table bb(b1 varchar(3),b2 varchar(1),b3 varchar(1))insert into aa
select '001','a','b' union all
select '002','c','d' union all
select '003','e','f'insert into bb
select '003','g','h' union all
select '004','i','j' select aa.*,bb.b2,bb.b3
from aa left join bb
on aa.a1=bb.b1
union all
select bb.*,'',''
from bb
where not exists (
select 1
from aa
where aa.a1=bb.b1
)
em.A2 as c2,em.A3 as c3,isnull(op.B2,'') as c4,isnull(op.B3,'') as c5 from a em left join b op
on em.A1=op.B1
union all
select *,'','' from b where B1='004'
)c手机发帖,测试数据就不插了
select a1=(case when a1 is null then b1 else a1 end),a2 as c2,a3 as c3,
b2 as c4,
b.b3 as c5
from a full join b
on a.a1=b.b1 where a2 is not null
union all
select a1=(case when a1 is null then b1 else a1 end),
b2 as c2,
b.b3 as c3,
a2 as c4,
a3 as c5
from a full join b
on a.a1=b.b1 where a2 is null and b2 is not null
7楼的,好像不能直接把B.1的记录直接放到C.1
9楼的,看上去是对的,还没验证,本来是两个EXCEL的表,如果不用写程序,直接用简单操作实现就好了。