一个查询问题 原来感觉不是很难但是又一直写不是出来
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-11 08:10:16
-- 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: )
--
----------------------------------------------------------------
--> 测试数据：[AEF]
if object_id('[AEF]') is not null drop table [AEF]
go
create table [AEF]([A] varchar(2),[E] varchar(2),[F] varchar(2))
insert [AEF]
select 'a1','e1','f1' union all
select 'a1','e1','f1' union all
select 'a2','e2','f2' union all
select 'a3','e3','f3' union all
select 'a3','e1','f3' union all
select 'a4','e1','f4'
--------------开始查询--------------------------
select DISTINCT * from [AEF] a WHERE EXISTS (SELECT 1 FROM [AEF] b WHERE a.a=b.a AND b.e='e1')
AND NOT EXISTS (SELECT 1 FROM [AEF] b WHERE a.a=b.a AND b.e<>'e1')
----------------结果----------------------------
/*
A E F
---- ---- ----
a1 e1 f1
a4 e1 f4
*/
