---------------------------------------------------------------- -- 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 行受影响)*/
-- 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 行受影响)*/