---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-31 17:59:30 -- Version: -- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) -- May 3 2005 23:18:38 -- Copyright (c) 1988-2003 Microsoft Corporation -- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] int,[C] int) insert [tb] select 1,1,2 union all select 2,1,3 union all select 3,2,4 union all select 4,2,4 union all select 5,3,5 union all select 6,4,5 --------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where b=t.b and c=t.c and a!=t.a) ----------------结果---------------------------- /* A B C ----------- ----------- ----------- 3 2 4 4 2 4(所影响的行数为 2 行)*/
select * from T a where (select count(*) from T where A=a.A and B=a.B)>1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-31 17:59:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,1,2 union all
select 2,1,3 union all
select 3,2,4 union all
select 4,2,4 union all
select 5,3,5 union all
select 6,4,5
--------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where b=t.b and c=t.c and a!=t.a)
----------------结果----------------------------
/*
A B C
----------- ----------- -----------
3 2 4
4 2 4(所影响的行数为 2 行)*/
where (select count(*) from T where A=a.A and B=a.B)>1