group by 和having count的怪问题 - 调试易

group by 和having count的怪问题

表如下:
ID    NAME
1     aa
1     bb
2     cc
我用
select * from table group by id having count(id)>1
结果是
id    name
1    aa
怎么样才能出来结果
id    name
1     aa
1     bb
我用mysql 5027
thanks.

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

/* try like this */select *
from 表 a
where exists(select 1 from 表 where id=a.id and name<>a.name);
/* or you may try like this */select * from aa
where id in (select id from aa group by id having count(*)>1);
第二个方法是个好方法,可惜我的是文本的
为什么MYSQL不支持我仍未应该支持的方法呢.