关于合并的sql难题，请爱新觉罗兄看看，谢谢

[wongwhb]

需求:
表A: finterid fitemid ficmointerid foutqty
001 001-1 09001001 20
001 001-2 09001001 50
表B：
finterid fitemid ficmointerid foutqty
001 001-1 09001001 80
001 001-2 09001001 100
001 001-3 09001001 200
001 001-4 09001001 150
现在合并得出这样的结果:
finterid fitemid ficmointerid foutqty
001 001-1 09001001 20
001 001-2 09001001 50
001 001-3 09001001 200
001 001-4 09001001 150
请问应怎样写sql语句啊？请各位兄弟帮助，谢谢了

[王向飞]

没看明白这是怎么合的？
只是把001 001-2 09001001 100
换成 001 001-2 09001001 50
?

[黄_瓜]

你是怎么合并的？

[fpzgm]

select * from a
union
select * from b 
where not exists(select 1 from a,b where a.finterid=b.finterid and a.fitemid=b.fitemid and a.ficmointerid=b.ficmointerid )

[wongwhb]

就是表B的outqty数里如果在表A中有对应的数量，就用表A的数量

[黄_瓜]

--> 测试数据：#ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([finterid] varchar(3),[fitemid] varchar(5),[ficmointerid] varchar(8),[foutqty] int)
insert #ta
select '001','001-1','09001001',20 union all
select '001','001-2','09001001',50
go

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([finterid] varchar(3),[fitemid] varchar(5),[ficmointerid] varchar(8),[foutqty] int)
insert #tb
select '001','001-1','09001001',80 union all
select '001','001-2','09001001',100 union all
select '001','001-3','09001001',200 union all
select '001','001-4','09001001',150
go

---->测试开始
select * from #ta
union all
select * from #tb b where 
not exists(
select 1 from #ta where[finterid]=b.[finterid]and[fitemid]=b.[fitemid] and [ficmointerid]=b.[ficmointerid] 
 
)
/*
finterid fitemid ficmointerid foutqty
-------- ------- ------------ -----------
001      001-1   09001001     20
001      001-2   09001001     50
001      001-3   09001001     200
001      001-4   09001001     150

(4 行受影响)


*/

[fpzgm]

create table a(finterid varchar(20), fitemid varchar(20), ficmointerid varchar(20),foutqty varchar(20))
create table b(finterid varchar(20), fitemid varchar(20), ficmointerid varchar(20),foutqty varchar(20))

insert into a
select '001', '001-1', '09001001', '20'
union select   '001', '001-2', '09001001' ,'50'


insert into b
select '001', '001-1', '09001001', '80'
union select   '001', '001-2', '09001001' ,'100'
union select   '001', '001-3', '09001001' ,'200'
union select   '001', '001-4', '09001001' ,'150'

  select * from a
union
select * from b 
where not exists
(select 1 from a 
where finterid=b.finterid and fitemid=b.fitemid and ficmointerid=b.ficmointerid )

[fpzgm]

create table a(finterid varchar(20), fitemid varchar(20), ficmointerid varchar(20),foutqty varchar(20))
create table b(finterid varchar(20), fitemid varchar(20), ficmointerid varchar(20),foutqty varchar(20))

insert into a
select '001', '001-1', '09001001', '20'
union select   '001', '001-2', '09001001' ,'50'


insert into b
select '001', '001-1', '09001001', '80'
union select   '001', '001-2', '09001001' ,'100'
union select   '001', '001-3', '09001001' ,'200'
union select   '001', '001-4', '09001001' ,'150'

  select * from a
union
select * from b 
where not exists
(select 1 from a 
where finterid=b.finterid and fitemid=b.fitemid and ficmointerid=b.ficmointerid )

/*
finterid fitemid ficmointerid foutqty
-------- ------- ------------ -----------
001      001-1   09001001     20
001      001-2   09001001     50
001      001-3   09001001     200
001      001-4   09001001     150

(4 行受影响)


*/

[王向飞]

小爱的数据

--> 测试数据：#ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([finterid] varchar(3),[fitemid] varchar(5),[ficmointerid] varchar(8),[foutqty] int)
insert #ta
select '001','001-1','09001001',20 union all
select '001','001-2','09001001',50
go

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([finterid] varchar(3),[fitemid] varchar(5),[ficmointerid] varchar(8),[foutqty] int)
insert #tb
select '001','001-1','09001001',80 union all
select '001','001-2','09001001',100 union all
select '001','001-3','09001001',200 union all
select '001','001-4','09001001',150
go



select isnull(a.[finterid],b.[finterid]) 
,isnull(a.[fitemid]  ,b.[fitemid])
,isnull(a.[ficmointerid],b.[ficmointerid])   
,isnull(a.[foutqty],b.[foutqty])
from #tb b left join #ta a
on b.fitemid=a.fitemid

[wongwhb]

谢谢各位兄弟