A表: DT DE
A1 D1
A2 D2
A3 D3
A3 D5
A5 D5B表
DT DE
A2 D2
A3 D5查出效果是:表A的DT,DE 与 表B的DT,DE相等的记录除排外。得出结果如下: DT DE
A1 D1
A3 D3
A5 D5
A1 D1
A2 D2
A3 D3
A3 D5
A5 D5B表
DT DE
A2 D2
A3 D5查出效果是:表A的DT,DE 与 表B的DT,DE相等的记录除排外。得出结果如下: DT DE
A1 D1
A3 D3
A5 D5
insert into a values('DT', 'DE')
insert into a values('A1', 'D1')
insert into a values('A2', 'D2')
insert into a values('A3', 'D3')
insert into a values('A3', 'D5')
insert into a values('A5', 'D5')
create table b(c1 varchar(10),c2 varchar(10))
insert into b values('DT', 'DE')
insert into b values('A2', 'D2')
insert into b values('A3', 'D5')
goselect a.* from a where not exists(select 1 from b where c1 = a.c1 and c2 = a.c2)drop table a , b/*
c1 c2
---------- ----------
A1 D1
A3 D3
A5 D5(所影响的行数为 3 行)*/
create table a(DT varchar(10),DE varchar(10))
insert into a values('A1', 'D1')
insert into a values('A2', 'D2')
insert into a values('A3', 'D3')
insert into a values('A3', 'D5')
insert into a values('A5', 'D5')
create table b(DT varchar(10),DE varchar(10))
insert into b values('A2', 'D2')
insert into b values('A3', 'D5')
goselect a.* from a where not exists(select 1 from b where DT = a.DT and DE = a.DE)drop table a , b/*
DT DE
---------- ----------
A1 D1
A3 D3
A5 D5(所影响的行数为 3 行)
*/
union
select * from Bexceptselect * from A
intersect
select * from B
--方法二
select * from a where dt not in
(select dt from b where dt=a.dt and de=a.de)
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(DT varchar(8), DE varchar(8))
insert into #a
select 'A1', 'D1' union all
select 'A2', 'D2' union all
select 'A3', 'D3' union all
select 'A3', 'D5' union all
select 'A5', 'D5'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(DT varchar(8), DE varchar(8))
insert into #b
select 'A2', 'D2' union all
select 'A3', 'D5' union all
select 'X2', 'Y2'select * from #A
union
select * from #B
except
select * from #A
intersect
select * from #B/*
DT DE
-------- --------
A1 D1
A3 D3
A5 D5
X2 Y2
*/
--总结我的两种方法并测试
create table a(dt varchar(10),de varchar(10))
insert into a values('A1', 'D1')
insert into a values('A2', 'D2')
insert into a values('A3', 'D3')
insert into a values('A3', 'D5')
insert into a values('A5', 'D5')
create table b(dt varchar(10),de varchar(10))
insert into b values('A2', 'D2')
insert into b values('A3', 'D5')
go--方法一:
select * from a where not exists (select 1 from b where a.dt=b.dt and a.de=b.de)--方法二:
select * from a where dt not in
(select dt from b where dt=a.dt and de=a.de)/*
DT DE
---------- ----------
A1 D1
A3 D3
A5 D5(所影响的行数为 3 行)
*/