MySQL列出重复数据和重复次数

2017-12-13 20:54:46

在MySQL中查找出列中的不同项很简单:

SELECT DISTINCT user_name FROM table_1;

查找重复的数据稍微复杂点:

SELECT user_name,count(*) as count from table_1 GROUP BY user_name having count>1; 

下面两个查询效率很低,数据量不大的时候可以用用

列出重复数据:

SELECT * FROM table_1 WHERE user_name IN (SELECT user_name FROM table_1 GROUP BY user_name having count(user_name)>1);

删除重复数据,只保留id最小的一条:

SELETE FROM table_1 WHERE user_name IN (SELECT user_name FROM table_1 GROUP BY puser_name having count(user_name)>1) AND min(id) NOT IN (SELECT id FROM table_1 GROUP BY user_name having count(user_name)>1);

近期文章