select A.name,A.peopleid from (select name,peopleid,count(distinct sex) as 'count' from people group by name,peopleid) as A where A.count = 2;(或A.count >= 2)作者: mysqlmpp123 时间: 2016-10-30 14:29
mysql> select b.name,a.peopleid from (select peopleid from people a group by peopleid having count(distinct sex)>1) a join people b on a.peopleid=b.peopleid order by a.peopleid;
+-----------+--------------------+
| name | peopleid |
+-----------+--------------------+
| 高会珍 | 410306197401010011 |
| 高会珍 | 410306197401010011 |
| 杨凤芹 | 410322194410161843 |
| 杨凤琴 | 410322194410161843 |
+-----------+--------------------+
4 rows in set (0.00 sec)