select * from table1 t where (select count(* ) from table1 where a=t.a and b=t.b and c=t.c)>1
--这样? ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-12-26 11:07:54 -- 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,[d] int) insert [tb] select 1,2,3,4 union all select 2,2,3,4 union all select 3,2,3,4 union all select 4,2,3,4 union all select 5,1,2,3 --------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d) or exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d) or exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d) or exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d<>t.d) ----------------结果---------------------------- /* a b c d ----------- ----------- ----------- ----------- 1 2 3 4 2 2 3 4 3 2 3 4 4 2 3 4(所影响的行数为 4 行) */
select * from [tb] where ltrim(a)+ltrim(b)+ltrim(c) in ( select ltrim(a)+ltrim(b)+ltrim(c) from [tb] group by ltrim(a)+ltrim(b)+ltrim(c) having count(1) >= 2 )
哦 只有a,b,c那这样. select * from [tb] t where exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d) or exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d) or exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-12-26 11:07:54 -- 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,[d] int) insert [tb] select 1,2,3,4 union all select 2,2,3,4 union all select 3,2,3,4 union all select 4,2,3,4 union all select 5,1,2,3 --------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where ltrim(a)+ltrim(b)+ltrim(c)<>ltrim(t.a)+ltrim(t.b)+ltrim(t.c) and d=t.d)----------------结果---------------------------- /* a b c d ----------- ----------- ----------- ----------- 1 2 3 4 2 2 3 4 3 2 3 4 4 2 3 4(所影响的行数为 4 行) */
from table1 t
where (select count(* ) from table1 where a=t.a and b=t.b and c=t.c)>1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-26 11:07:54
-- 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,[d] int)
insert [tb]
select 1,2,3,4 union all
select 2,2,3,4 union all
select 3,2,3,4 union all
select 4,2,3,4 union all
select 5,1,2,3
--------------开始查询--------------------------select
*
from
[tb] t
where
exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d)
or
exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d)
or
exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d)
or
exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d<>t.d)
----------------结果----------------------------
/* a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 2 3 4
3 2 3 4
4 2 3 4(所影响的行数为 4 行)
*/
where ltrim(a)+ltrim(b)+ltrim(c)
in
(
select ltrim(a)+ltrim(b)+ltrim(c)
from [tb]
group by ltrim(a)+ltrim(b)+ltrim(c)
having count(1) >= 2
)
select
*
from
[tb] t
where
exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d)
or
exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d)
or
exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-26 11:07:54
-- 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,[d] int)
insert [tb]
select 1,2,3,4 union all
select 2,2,3,4 union all
select 3,2,3,4 union all
select 4,2,3,4 union all
select 5,1,2,3
--------------开始查询--------------------------select
*
from
[tb] t
where
exists(select 1 from tb where ltrim(a)+ltrim(b)+ltrim(c)<>ltrim(t.a)+ltrim(t.b)+ltrim(t.c) and d=t.d)----------------结果----------------------------
/* a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 2 3 4
3 2 3 4
4 2 3 4(所影响的行数为 4 行)
*/