如何筛选出一张表中不在另两张表里的数据？

Question

有三张表，A，B，C。A中数据如下：PN         SN           BN          Date
0001       AAA          MMM         2010-01-01
0001       AAA          MMF         2010-01-02
0001       AAA          MMK         2010-01-03
0002       AAA          KKM         2010-01-01
0002       AAA          KKE         2010-01-02
0002       AAA          MMC         2010-01-03
0003       AAA          MMU         2010-01-01
0003       AAA          MMH         2010-01-02
0003       AAA          KKW         2010-01-03 
0003       AAA          KKY         2010-01-04B中数据如下：
PN         SN           BN          Date
0001       AAA          MMM         2010-01-01     
0002       AAA          KKM         2010-01-01C中数据如下：
PN         SN           BN          Date
0002       AAA          MMC         2010-01-03
0003       AAA          KKW         2010-01-03要求查询表A中没有再B、C中的全部记录
运行后的结果是：
PN         SN           BN          Date
0001       AAA          MMF         2010-01-02
0001       AAA          MMK         2010-01-03
0002       AAA          KKE         2010-01-02
0003       AAA          MMU         2010-01-01
0003       AAA          MMH         2010-01-02
0003       AAA          KKW         2010-01-03这个应该如何写呢？ 

Answer 1

select * from a
where not exists(select 1 from b where a.pn=b.pn land a.sn=b.sn and a.bn=b.bn)
    and not exists(select 1 from c where a.pn=c.pn land a.sn=c.sn and a.bn=c.bn)

Answer 2

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-27 14:03:40
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [a]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0001','AAA','MMF',N'2010-01-02' UNION ALL
SELECT '0001','AAA','MMK',N'2010-01-03' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKE',N'2010-01-02' UNION ALL
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','MMU',N'2010-01-01' UNION ALL
SELECT '0003','AAA','MMH',N'2010-01-02' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKY',N'2010-01-04'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [b]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01'
GO
--SELECT * FROM [b]--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [c]
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03'
GO
--SELECT * FROM [c]-->SQL查询如下:
select * from a
where not exists(select 1 from b where a.pn=b.pn and a.sn=b.sn and a.bn=b.bn)
    and not exists(select 1 from c where a.pn=c.pn and a.sn=c.sn and a.bn=c.bn)
/*
PN         SN         BN         Date
---------- ---------- ---------- -----------------------
0001       AAA        MMF        2010-01-02 00:00:00.000
0001       AAA        MMK        2010-01-03 00:00:00.000
0002       AAA        KKE        2010-01-02 00:00:00.000
0003       AAA        MMU        2010-01-01 00:00:00.000
0003       AAA        MMH        2010-01-02 00:00:00.000
0003       AAA        KKY        2010-01-04 00:00:00.000(6 行受影响)
*/

Answer 3

--也可用left join 或right join
--找出在table1中且不在table2中的ID
字段:
table1:ID
table2:ID
select a.*
from table1 a
     left join table2 b
on a.ID = b.ID
where b.ID is null

Answer 4

not exists效率太低，当然使用left join 了

Answer 5

---借TONY哥的数据
--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-27 14:03:40
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
    DROP TABLE [a]
GO
CREATE TABLE [a]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [a]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0001','AAA','MMF',N'2010-01-02' UNION ALL
SELECT '0001','AAA','MMK',N'2010-01-03' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKE',N'2010-01-02' UNION ALL
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','MMU',N'2010-01-01' UNION ALL
SELECT '0003','AAA','MMH',N'2010-01-02' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKY',N'2010-01-04'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
    DROP TABLE [b]
GO
CREATE TABLE [b]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [b]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01'
GO
--SELECT * FROM [b]--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
    DROP TABLE [c]
GO
CREATE TABLE [c]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [c]
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03'
GO
--SELECT * FROM [c]-->SQL查询如下:
SELECT
 * 
FROM
 a 
WHERE
  CHECKSUM(*) NOT IN (SELECT CHECKSUM(*) FROM b)
AND
  CHECKSUM(*) NOT IN (SELECT CHECKSUM(*) FROM C)/*
PN         SN         BN         Date
---------- ---------- ---------- -----------------------
0001       AAA        MMF        2010-01-02 00:00:00.000
0001       AAA        MMK        2010-01-03 00:00:00.000
0002       AAA        KKE        2010-01-02 00:00:00.000
0003       AAA        MMU        2010-01-01 00:00:00.000
0003       AAA        MMH        2010-01-02 00:00:00.000
0003       AAA        KKY        2010-01-04 00:00:00.000(6 行受影响)
*/

Answer 6

CHECKSUM(*) 能够生成哈希索引

Answer 7

select * from A except (select * from B union select * from C)

Answer 8

IF NOT OBJECT_ID('[a]') IS NULL
    DROP TABLE [a]
GO
CREATE TABLE [a]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [a]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0001','AAA','MMF',N'2010-01-02' UNION ALL
SELECT '0001','AAA','MMK',N'2010-01-03' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKE',N'2010-01-02' UNION ALL
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','MMU',N'2010-01-01' UNION ALL
SELECT '0003','AAA','MMH',N'2010-01-02' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKY',N'2010-01-04'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
    DROP TABLE [b]
GO
CREATE TABLE [b]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [b]
SELECT '0001','AAA','MMM',N'2010-01-01' UNION ALL
SELECT '0002','AAA','KKM',N'2010-01-01'
GO
--SELECT * FROM [b]--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
    DROP TABLE [c]
GO
CREATE TABLE [c]([PN] NVARCHAR(10),[SN] NVARCHAR(10),[BN] NVARCHAR(10),[Date] DATETIME)
INSERT [c]
SELECT '0002','AAA','MMC',N'2010-01-03' UNION ALL
SELECT '0003','AAA','KKW',N'2010-01-03'
GOselect * from a
except
(
select * from b
union all
select * from c)PN         SN         BN         Date
---------- ---------- ---------- -----------------------
0001       AAA        MMF        2010-01-02 00:00:00.000
0001       AAA        MMK        2010-01-03 00:00:00.000
0002       AAA        KKE        2010-01-02 00:00:00.000
0003       AAA        KKY        2010-01-04 00:00:00.000
0003       AAA        MMH        2010-01-02 00:00:00.000
0003       AAA        MMU        2010-01-01 00:00:00.000(6 行受影响)

Answer 9

orselect * from a
except
select * from b
except
select * from cPN         SN         BN         Date
---------- ---------- ---------- -----------------------
0001       AAA        MMF        2010-01-02 00:00:00.000
0001       AAA        MMK        2010-01-03 00:00:00.000
0002       AAA        KKE        2010-01-02 00:00:00.000
0003       AAA        KKY        2010-01-04 00:00:00.000
0003       AAA        MMH        2010-01-02 00:00:00.000
0003       AAA        MMU        2010-01-01 00:00:00.000(6 行受影响)

Answer 10

东升哥这个暴强！！！Except关键字我从来都没有用过......

Answer 11

http://id.91cool.net/sign/?name=%CE%DE%D3%C7&say=%B2%C5%B8%DF%C6%DF%B0%CB%B6%B7,%D1%A7%B8%BB%CE%E5%C1%F9%B3%B5,%B8%C4%D0%D0%C8%FD%CB%C4%B4%CE,%D4%C2%C8%EB%D2%BB%B6%FE%C7%A7! 

Answer 12

大家的方法都很强大！
小F MM 的做法我也从未见过！
好好学习一下！