现有以下这样的表：
id name address
1 A ABCD
2 A EFG
3 B HIJ
4 C KLM
5 B ABCD
想name字段重复的，adress字段值不为"ABCD" 的去除，要如何写SQL语句？
最终效果为：
id name address
1 A ABCD
4 C KLM
5 B ABCD
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-26 15:48:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
-- Jun 28 2012 08:36:30
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] nvarchar(2),[address] nvarchar(8))
insert [huang]
select 1,'A','ABCD' union all
select 2,'A','EFG' union all
select 3,'B','HIJ' union all
select 4,'C','KLM' union all
select 5,'B','ABCD'
--------------生成数据--------------------------
SELECT * FROM huang WHERE id NOT IN (
SELECT id
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select name,COUNT(1)[cnt]
from [huang]
GROUP BY name
HAVING COUNT(1)>1)b WHERE a.NAME=b.NAME )
AND [ADDRESS]<>'ABCD')
----------------结果----------------------------
/*
id name address
----------- ---- --------
1 A ABCD
4 C KLM
5 B ABCD
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-06-26 15:55:37
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[address] varchar(4))
insert [tb]
select 1,'A','ABCD' union all
select 2,'A','EFG' union all
select 3,'B','HIJ' union all
select 4,'C','KLM' union all
select 5,'B','ABCD'
--------------开始查询--------------------------
select * from tb as t where not exists(select 1 from tb where name=t.name and id<>t.id and t.address<>'ABCD')
----------------结果----------------------------
/* id name address
----------- ---- -------
1 A ABCD
4 C KLM
5 B ABCD
(3 行受影响)
*/