create table tab(col1 int,col2 varchar(10)) insert tab select 1,'x' union select 2,'y' union select 3,'y'select * from tab t where (select count(1) from tab where t.col2=col2)>1
select * from test1 where c2 in ( select c2 from test1 group by c2 having count(*)>1 ) and c1 not in ( select c1 from test1 group by c1 having count(*)>1
)
希望得出的结果如下: col1 col22 y 3 y
select * from tab t where (select count(1) from tab where t.col2=col2)>1 好像不行哦,如果第一列相同,用上面的语句的话,也查出来了.
用我的方法是对的.不过效率可能不行. select * from test1 where c2 in ( select c2 from test1 group by c2 having count(*)>1 ) and c1 not in ( select c1 from test1 group by c1 having count(*)>1
insert tab
select 1,'x'
union select 2,'y'
union select 3,'y'select * from tab t where (select count(1) from tab where t.col2=col2)>1
( select c2 from test1 group by c2 having count(*)>1 )
and
c1 not in
(
select c1 from test1 group by c1 having count(*)>1
)
col1 col22 y
3 y
好像不行哦,如果第一列相同,用上面的语句的话,也查出来了.
select * from test1 where c2 in
( select c2 from test1 group by c2 having count(*)>1 )
and
c1 not in
(
select c1 from test1 group by c1 having count(*)>1
)2 y
3 y