高手帮忙，SQL 查询满足条件的记录并再另一个表不存在的记录

[tomyi]

比如：
表1：
ID_Wafer ID_ZhCh
1 1
2 1
3 2

表2：

ID_Wafer ID_ZhCh
2 1

要求：
查询表1中 的ID_ZhCH = 1 且表2不存在的ID_Wafer
示例中查询的结果是：
ID_Wafer ID_ZhCh
1 1

刚开始学SQL,只会一些简单的查询，望高手帮忙，看看能否通过SQL语句直接实现。

谢谢了。

[xuam]

select *  from  t1 where ID_ZhCH = 1 and not exsits (select * from T2)

[--小F--]

select * from a where ID_ZhCH = 1 and not exists(select 1 from b where ID_Wafer=a.ID_Wafer)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-30 16:48:46
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID_Wafer] varchar(8),[ID_ZhCh] varchar(7))
insert [a]
select '1','1' union all
select '2','1' union all
select '3','2'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([ID_Wafer] int,[ID_ZhCh] int)
insert [b]
select 2,1
--------------开始查询--------------------------
select * from a where ID_ZhCH = 1 and not exists(select 1 from b where ID_Wafer=a.ID_Wafer)
----------------结果----------------------------
/* ID_Wafer ID_ZhCh
-------- -------
1        1

(1 行受影响)
*/

[快溜]

select * from a where ID_ZhCH = 1
except
select * from b

[执木]

select * from Table1 where ID_Wafer not in(select ID_Wafer from Table2)

[tomyi]

谢谢各位的热情解答。

又进步一点点了。

[ithhh2012]

select * from t1 where id_wafer=1 and id_wafer not in (select id_wafer from t2)

[tomyi]

根据上面的帮助，我已经写出了我想要的查询语句。

在此，谢谢大家的帮助。

--1. 查询记录
Select
ZhLGD.Name
,Route.Name
,[1ZhChD].Name
,Wafer.Name
From
ZhLGD
,Route
,[1ZhChD]
,Wafer
,[10001]
-- ,[10020]
Where
[10001].[ID_ZhLGD] = ZhLGD.[ID_ZhLGD]
and [10001].[ID_Route] = Route.[ID_Route]
and [10001].[ID_ZhChD] = [1ZhChD].[ID_ZhChD]
and [10001].[ID_Wafer] = Wafer.[ID_Wafer]
AND [10001].ID_ZhChD
IN (
SELECT
[1ZhChD].ID_ZhChD
FROM
[1ZhChD]
WHERE
[1ZhChD].[Name] LIKE 'BACC14%')
AND [10001].ID_IOState
IN (
SELECT
IOState.ID_IOState
FROM
IOState
WHERE
IOState.Name = '出站')
AND [10001].ID_PQS
IN (
SELECT
PQS.ID_PQS
FROM
PQS
WHERE
PQS.Name = '正常')
AND [10001].ID_Wafer
NOT IN (
SELECT
[10020].ID_Wafer
FROM
[10020]
WHERE
[10020].ID_ZhChD
IN (
Select
[1ZhChD].[ID_ZhChD]
FROM
[1ZhChD]
WHERE
[1ZhChD].[Name] LIKE 'BACC14%'
)
)
ORDER BY [1ZhChD].Name ASC ,[Wafer].Name ASC

-- 共筛选出:7 条记录

[tomyi]

根据上面的帮助，我已经写出了我想要的查询语句。

在此，谢谢大家的帮助。

--1. 查询记录
Select  
	 ZhLGD.Name
	,Route.Name
	,[1ZhChD].Name
	,Wafer.Name 
From 
	 ZhLGD
	,Route
	,[1ZhChD]
	,Wafer 
	,[10001] 
--	,[10020]  
Where  
	[10001].[ID_ZhLGD] = ZhLGD.[ID_ZhLGD] 
and [10001].[ID_Route] = Route.[ID_Route] 
and [10001].[ID_ZhChD] = [1ZhChD].[ID_ZhChD] 
and [10001].[ID_Wafer] = Wafer.[ID_Wafer]  
AND [10001].ID_ZhChD 
	IN (
	SELECT 
		[1ZhChD].ID_ZhChD 
	FROM 
		[1ZhChD] 
	WHERE 
		[1ZhChD].[Name] LIKE 'BACC14%') 
AND [10001].ID_IOState 
	IN (
	SELECT 
		IOState.ID_IOState 
	FROM 
		IOState 
	WHERE 
		IOState.Name = '出站') 
AND  [10001].ID_PQS 
	IN ( 
		SELECT 
			PQS.ID_PQS 
		FROM 
			PQS 
		WHERE 
			PQS.Name = '正常') 
AND [10001].ID_Wafer 
	NOT IN (
	SELECT 
		[10020].ID_Wafer 
	FROM 
		[10020] 
	WHERE 
		[10020].ID_ZhChD 
			IN (
				Select 
					[1ZhChD].[ID_ZhChD] 
				FROM 
					[1ZhChD] 
				WHERE 
					[1ZhChD].[Name] LIKE 'BACC14%'
			)
	)
ORDER BY [1ZhChD].Name ASC ,[Wafer].Name ASC
  
-- 共筛选出:7 条记录

[-晴天]

恭喜!