博客文章 首页 > 博客文章列表 >

MySQL根据某一个或者多个字段查找重复数据,删除重复数据

一、根据projectNumber和deviceNumber字段找到重复记录


SELECT * from (SELECT *, CONCAT(projectNumber,deviceNumber) as nameAndCode from em_camerainfo) t WHERE t.nameAndCode in 
(
    SELECT nameAndCode from (SELECT CONCAT(projectNumber,deviceNumber) as nameAndCode from em_camerainfo) tt GROUP BY nameAndCode HAVING count(nameAndCode) > 1
)

2、删除重复记录,只保留id字段值最大的记录

DELETE from em_camerainfo WHERE id not in 
(
    SELECT maxid from (SELECT MAX(id) as maxid, CONCAT(projectNumber,deviceNumber) as nameAndCode from em_camerainfo GROUP BY nameAndCode) t
)

如遇页面排版混乱,请访问原网页   访问原网页