27,579
社区成员
发帖
与我相关
我的任务
分享
;with A(AA,BB)
as
(
select '12345-01279', '南庄村'
union all select '12300-88877', '山西镇'
union all select '33344-00011', '阿村'
),
B(aa,bb)
as
(
select '12345-01279', '南庄村'
union all select '12300-88877', '天山'
union all select '12009-77000', '上风寨'
union all select '74822-33311', '一平沟'
)
select aa,bb from A
union
select aa,bb from B
where not exists(select 1 from a where a.AA = b.aa)
order by
/*
aa bb
12009-77000 上风寨
12300-88877 山西镇
12345-01279 南庄村
33344-00011 阿村
74822-33311 一平沟
*/
select aa,bb from A
union
select aa,bb from B
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 10:54:55
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([AA] varchar(11),[BB] varchar(6))
insert [A]
select '12345-01279','南庄村' union all
select '12300-88877','山西镇' union all
select '33344-00011','阿村'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([AA] varchar(11),[BB] varchar(6))
insert [B]
select '12345-01279','南庄村' union all
select '12300-88877','天山' union all
select '12009-77000','上风寨' union all
select '74822-33311','一平沟'
--------------开始查询--------------------------
SELECT * FROM a
UNION ALL
SELECT * FROM B
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.aa=b.aa)
----------------结果----------------------------
/*
AA BB
----------- ------
12345-01279 南庄村
12300-88877 山西镇
33344-00011 阿村
12009-77000 上风寨
74822-33311 一平沟
*/
select b.aa as baa,b.bb as bbb,a.aa as aaa,a.bb as abb
from b
left join a
on b.aa=b.aa