这样查询出的结果是a b c ----------------- 1 2 3 1 2 2 1 1 2 2 1 2 2 2 2我想要的结果是a b c ----------------- 1 2 3
少了个条件 having count(*) > 1
select distinct * from table1 a where exists(select 1 from table1 where a=a.a and b=a.b and c=a.c)
create table table1(a int,b int ,c int) insert table1 select 1, 2, 3 union all select 1, 2, 2 union all select 1, 1, 2 union all select 2, 1, 2 union all select 2, 2, 2 union all select 1, 2, 3 select distinct * from table1 t where 1<(select count(1) from table1 where t.a=a and t.b = b and t.c=c)
select a,b,c from table1 t where 1<(select count(1) from table1 where t.a=a and t.b = b and t.c=c) group by a,b,c
--try select * from T where a<>b and a<>c and b<>c
create table T(a int, b int, c int)insert T select 1, 2, 3 union all select 1, 2, 2 union all select 1, 1, 2 union all select 2, 1, 2 union all select 2, 2, 2 select * from T where a<>b and a<>c and b<>c--result a b c ----------- ----------- ----------- 1 2 3(1 row(s) affected)
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码) 最新版本:20070130http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
-----------------
1 2 3
1 2 2
1 1 2
2 1 2
2 2 2我想要的结果是a b c
-----------------
1 2 3
insert table1
select 1, 2, 3
union all
select 1, 2, 2
union all
select 1, 1, 2
union all
select 2, 1, 2
union all
select 2, 2, 2
union all
select 1, 2, 3
select distinct * from table1 t where 1<(select count(1) from table1 where t.a=a and t.b = b and t.c=c)
from table1 t
where 1<(select count(1) from table1 where t.a=a and t.b = b and t.c=c)
group by a,b,c
select * from T
where a<>b and a<>c and b<>c
union all select 1, 2, 2
union all select 1, 1, 2
union all select 2, 1, 2
union all select 2, 2, 2
select * from T
where a<>b and a<>c and b<>c--result
a b c
----------- ----------- -----------
1 2 3(1 row(s) affected)