22,301
社区成员




--> 测试数据:#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
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 行受影响)
*/
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 )
--> 测试数据:#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 行受影响)
*/
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 )