TABLE 1：
ID1 ID2 IQTY
A , F , 12
A , H , 10
...
TABLE 2：
ID1 ID2 IQTY
A , F , 5
A , D , 2
结果：
ID1 ID2 IQTY
A , F , 5
A , D, 2
A, H , 10
即取TABLE2所有行 和 不包含前两个字段ID1，ID2相同的TABLE1的行
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-24 09:27:31
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[TABLE1]
if object_id('[TABLE1]') is not null drop table [TABLE1]
go
create table [TABLE1]([ID1] nvarchar(2),[ID2] nvarchar(2),[IQTY] int)
insert [TABLE1]
select 'A','F',12 union all
select 'A','H',10
--> 测试数据[TABLE2]
if object_id('[TABLE2]') is not null drop table [TABLE2]
go
create table [TABLE2]([ID1] nvarchar(2),[ID2] nvarchar(2),[IQTY] int)
insert [TABLE2]
select 'A','F',5 union all
select 'A','D',2
--------------生成数据--------------------------
select * from [TABLE2]
UNION ALL
select * from [TABLE1] a
WHERE NOT EXISTS (SELECT 1 FROM [TABLE2] b WHERE a.id1=b.id1 AND a.id2=b.id2)
----------------结果----------------------------
/*
ID1 ID2 IQTY
---- ---- -----------
A F 5
A D 2
A H 10
*/
------解决方案--------------------
/*
TABLE 1：
ID1 ID2 IQTY
A , F , 12
A , H , 10
...
TABLE 2：
ID1 ID2 IQTY
A , F , 5
A , D , 2
*/
create table #a (ID1 char(1),ID2 char(2),IQTY TINYINT)
create table #b (ID1 char(1),ID2 char(2),IQTY TINYINT)
insert into #a (ID1,ID2,IQTY)
VALUES('A','F',12),
('A','H',12)
insert into #B (ID1,ID2,IQTY)
VALUES('A','F',5),
('A','D',2)
--解决方案
;with a as (
select * from #a
union all
select * from #b
)
select distinct a.id1,a.id2
,case WHEN B.IQTY IS NULL THEN A.IQTY ELSE B.IQTY END AS IQTY
from a left outer join #b as b on a.id1 = b.id1 and a.id2 = b.id2
--结果展示
/*
id1 id2 IQTY
---- ---- ----
A D 2
A F 5
A H 12
(3 行受影响)
*/
------解决方案--------------------
SELECT ID1, ID2, IQTY FROM [TABLE2]
UNION ALL
SELECT ID1, ID2, IQTY FROM [TABLE1]
WHERE ID1+ID2 NOT IN (SELECT ID1+ID2 FROM [TABLE1])