如何一次查询出某一字段相同的全部数据

Question

1.如下表

age	phone	name	address	sex	type
12	13566	mike	Guangzhou	man	people
13	13721	mike	Beijing	man	people
14	13412	leo	Shanghai	man	people
15	19011	kitty	Chengdu	women	people
16	18098	kitty	Beijing	women	people
17	18666	kitty	Shanghai	women	people
18	13399	jhon	Hainan	man	people

2.如何一次并高效查询出所有重名用户的所有信息（希望数据如下）

age	phone	name	address	sex	type
12	13566	mike	Guangzhou	man	people
13	13721	mike	Beijing	man	people
15	19011	kitty	Chengdu	women	people
16	18098	kitty	Beijing	women	people
17	18666	kitty	Shanghai	women	people

Answer 1

假设你的表名是user：

select * from user where name in (select name from user group by name having count(1) > 1);

Answer 2

select * from user group by (...) having count(*) > 1就足够了，不需要再用in。
其实高效什么的你真的想太多了，这属于设计失误，花时间修复一次然后修改表结构防止此类问题再次发生才是正根。

另外这个表没有id，所以你也不能自动删除重复的数据，因为写不出条件，只能再用中间表去倒。

综上，别考虑效率了，手工改正就好。

Answer 3

SELECT * FROM user AS a
WHERE EXISTS (
SELECT 1 FROM user AS b
WHERE a.name = b.name
GROUP BY name
HAVING COUNT(1) > 1
)

由于涉及到全表GROUP BY，所以性能比较低。
如果可以修改表结构的话，建议增加一个字段表示该用户名称是否重名，该字段在写入数据的时候就可以判断维护了。

Answer 4

数据库的世界没有魔法，索引之所以能加快搜索速度就是因为它预先处理了，用延长每次插入更新数据的时间来加快搜索速度，都是有代价的。

回到这个问题，你可以在name上加索引，这样group by就能更高效一点，但是根据你的需求还是要子查询。

不过在name上加索引我觉得不是个健康的做法。如果是我，解决这个问题会做一个定时任务：扫描重名的用户，把ID记录在某个表A上。然后页面上显示时直接把表A拿出来关联用户表即可。之所以这样做还有一个原因就是这个工作并非要做到很严格，和实际有点误差是无妨的。