MySql根据字段名查询重复记录并删除！只保留一条查询重复记录

最近在处理业务数据的时候！在几W条记录里存在着些相同的记录,如何用SQL语句,删除掉重复的呢?

可以用以下方法进行处理

其实很简单！就是查找表中多余的重复记录，重复记录是根据单个字段来查询、然后删除其他重复的记录即可

查询重复记录

SELECT  id ,title from  topic GROUP BY title HAVING count(*) > 1;

这样查出来的结果比较乱

查询全部的重复记录并分组

SELECT
  * 
FROM
  topic 
WHERE
  title IN ( SELECT title FROM topic GROUP BY  title HAVING Count(*)> 1 ) 
  AND title IN (
  SELECT
    title 
  FROM
    topic 
  GROUP BY
    title
HAVING
  count(*)> 1) ORDER BY title

对查询结果进行分组根据需要的字段 

查询除第一条外的多余数据

SELECT
  *
FROM
  topic
WHERE
  id NOT IN (
    SELECT
      dt.minId
    FROM
      (
        SELECT
          MIN(id) AS minId
        FROM
          topic
        GROUP BY
        title
      ) dt
  )
ORDER BY
  title

删除第一条外的多余数据

DELETE
FROM
  topic
WHERE
  id NOT IN (
    SELECT
      dt.minId
    FROM
      (
        SELECT
          MIN(id) AS minId
        FROM
          topic
        GROUP BY
          title
      ) dt
  )
ORDER BY
  title