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

wongwhb 卡莱 2010-10-22 01:25:11
需求:
表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语句啊?请各位兄弟帮助,谢谢了
...全文
53 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wongwhb 2010-10-22
谢谢各位兄弟
回复
王向飞 2010-10-22
小爱的数据
--> 测试数据:#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
回复
fpzgm 2010-10-22

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 行受影响)


*/

回复
fpzgm 2010-10-22

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 )
回复
小_爱 2010-10-22
--> 测试数据:#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 行受影响)


*/
回复
wongwhb 2010-10-22
就是表B的outqty数里如果在表A中有对应的数量,就用表A的数量
回复
fpzgm 2010-10-22

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 )
回复
小_爱 2010-10-22
你是怎么合并的?

回复
王向飞 2010-10-22
没看明白这是怎么合的?
只是把001 001-2 09001001 100
换成 001 001-2 09001001 50
?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-22 01:25
社区公告
暂无公告