mysql查找重复的行

例子：查找重复的电子邮箱

创建 email表，并插入如下三行数据

-- 创建表
CREATE TABLE email(
id INT NOT NULL auto_increment,
email VARCHAR(20) NOT NULL,
PRIMARY KEY(id));

-- 插入数据
INSERT into email(email) VALUES
("a123@.com"),
("b123@.com"),
("a123@.com"),
("d123@.com");

---查找重复的电子邮箱：
SELECT email
FROM email
GROUP BY email
HAVING COUNT(email)>1;

通过having查询重复行：

select id from t_vote group by bid having (count(bid) >1 and count(uid) > 1)

复杂的用法

select a.ip,count(a.ip) from (

  select createdAt,ip,count(createdAt),count(ip) from t_web_access_record where 
  TO_DAYS(createdAt)=TO_DAYS(now()) 
  group by createdAt,ip having (count(createdAt) >2 and count(ip) > 2)

) a
GROUP BY a.ip
order by count(a.ip) desc;

删除重复数据

删除重复的列，只保留id最小的一行

delete from person where uniqle_id in (
select * from
(select min(uniqle_id) from person group by uniqle_id having count(uniqle_id) > 1) temp
)
and id not in 
(select * from 
(select min(id) from person group by uniqle_id having count(uniqle_id)> 1 )temp2
)

删除所有重复的行

delete from person where uniqle_id in (
select * from
(select min(uniqle_id)as uniqle_id from person group by uniqle_id having count(uniqle_id) > 1) temp
)