«

mysql 查询重复字段统计重复个数

---

时间：2021-10-28    作者：网络剑客    分类： mysql

---

需要在MySQL中查询某个字段属性值重复的次数：
select mobile , count(*) AS count from member group by mobile order by count DESC

mysql 查询重复字段统计重复个数
select Mobile,count(*) as a from member group by Mobile having count(1)>1

删除重复的数据呢只剩下一条
DELETE FROM chat_test WHERE name IN ( SELECT h1.name FROM (SELECT name FROM chat_test GROUP BY name HAVING COUNT(1)>1 ) AS h1 ) AND id NOT IN ( SELECT h2.id FROM ( SELECT id FROM chat_test GROUP BY name HAVING COUNT(1)>1 ) AS h2)

先把以上查询出来的结果当做条件 name in 查询的结果集 之后 在AND id NOT in (id 字段) 那么现在就是name=结果集 ADN id!=结果集即可

标签: mysql