我有表A
字段 a,b,c,d,e,f表B
字段 a ,c,e,g,i我想要得到一个结果集,把表A中a,c,e字段的值在表B中也存在的记录去除。
例如表A:
a,b,c,d,e,f
1,2,3,4,5,6
2,3,4,5,6,7
3,4,5,6,7,8
4,5,6,7,8,9表B
a,c,e,g,i
3,5,7,7,8
4,6,8,8,9
3,4,5,6,7
6,7,7,6,4则应该得到
1,2,3,4,5,6
2,3,4,5,6,7
字段 a,b,c,d,e,f表B
字段 a ,c,e,g,i我想要得到一个结果集,把表A中a,c,e字段的值在表B中也存在的记录去除。
例如表A:
a,b,c,d,e,f
1,2,3,4,5,6
2,3,4,5,6,7
3,4,5,6,7,8
4,5,6,7,8,9表B
a,c,e,g,i
3,5,7,7,8
4,6,8,8,9
3,4,5,6,7
6,7,7,6,4则应该得到
1,2,3,4,5,6
2,3,4,5,6,7
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-20 22:03:49
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([a] int,[c] int,[e] int,[g] int,[i] int)
insert [B]
select 3,5,7,7,8 union all
select 4,6,8,8,9 union all
select 3,4,5,6,7 union all
select 6,7,7,6,4
--------------开始查询----------------------------select * from [B]--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([a] int,[b] int,[c] int,[d] int,[e] int,[f] int)
insert [A]
select 1,2,3,4,5,6 union all
select 2,3,4,5,6,7 union all
select 3,4,5,6,7,8 union all
select 4,5,6,7,8,9
--------------开始查询--------------------------
SELECT * FROM A a WHERE EXISTS (SELECT 1 FROM (
select a,c,e from [A]
EXCEPT
SELECT a,c,e FROM B) b WHERE a.a=b.a AND a.c=b.c AND a.e=b.e)
----------------结果----------------------------
/*
a b c d e f
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 6
2 3 4 5 6 7(2 行受影响)
*/
SELECT *
FROM A a
WHERE NOT EXISTS (SELECT TOP 1 1 FROM b WHERE a.a=b.a AND a.c=b.c AND a.e=b.e)
left join B y
on x.a = y.a
and x.c = y.c
and x.e = y.e
where y.a is not null;
Author:James
Desc:去除两表相同的数据
*/if object_id('[B]') is not null
drop table [B]
go
create table [B]([a] int,[c] int,[e] int,[g] int,[i] int)
insert [B]
select 3,5,7,7,8
union ALL
select 4,6,8,8,9
union ALL
select 3,4,5,6,7
union ALL
select 6,7,7,6,4if object_id('[A]') is not null
drop table [A]
go
create table [A]([a] int,[b] int,[c] int,[d] int,[e] int,[f] int)
insert [A]
select 1,2,3,4,5,6
union ALL
select 2,3,4,5,6,7
union ALL
select 3,4,5,6,7,8
union ALL
select 4,5,6,7,8,9---方法1
SELECT a,c,e FROM [A] EXCEPT (SELECT a,c,e FROM [B])
---方法2
SELECT [A].* FROM [A] LEFT JOIN [B]
on [A].a = [B].a AND [A].c = [B].c AND [A].e = [B].e
WHERE [B].a IS NULL AND [B].c IS NULL AND [B].e IS NULL
SELECT TOP 1 1 FROM b WHERE a.a=b.a AND a.c=b.c AND a.e=b.e这条语句会查出什么内容来?