mysql删除重复记录，保存Id最小的一条

-- 尝试使用临时表，进程断掉，数据都没了

   CREATE  TABLE midtemp AS SELECT MIN(aid) AS minid FROM dede_addoninfos GROUP BY title;
   
   DELETE FROM dede_addoninfos WHERE aid NOT IN (SELECT minid  FROM midtemp);
   
   SELECT * FROM dede_addoninfos 

-- 处理shop表数据记录

  CREATE  TABLE shoptemp AS SELECT MIN(id) AS minid FROM dede_archives WHERE typeid=8  GROUP BY title;
  
  DELETE FROM dede_addonshop WHERE aid NOT IN (SELECT minid  FROM shoptemp);
  
    DELETE FROM dede_archives WHERE typeid=8 AND id NOT IN (SELECT minid  FROM shoptemp);


  SELECT *   FROM dede_addonshop  

-- 写的顺序：select ... from... where.... group by... having... order by..
-- 执行顺序：from... where...group by... having.... select ... order by...


/*一、方法1
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。*/


-- 1、创建临时表midtemp


SELECT * FROM dede_addoninfos


CREATE TEMPORARY TABLE midtemp AS 
SELECT * FROM dede_addoninfos WHERE aid IN(
SELECT MIN(aid) FROM dede_addoninfos 
GROUP BY title
HAVING COUNT(title)>1
ORDER BY COUNT(title));


SELECT * FROM midtemp


-- 2、清空数据表


TRUNCATE TABLE dede_addoninfos 


SELECT * FROM dede_addoninfos 


-- 3 插入数据


INSERT INTO dede_addoninfos  SELECT *  FROM midtemp




-- 方法二、按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下


SELECT * FROM dede_arctiny


 CREATE TEMPORARY TABLE aidtedmp AS SELECT MIN(aid) AS MID  FROM dede_addoninfos GROUP BY title 
 
 SELECT  *  FROM aidtedmp
 
 DELETE FROM  dede_addoninfos WHERE aid  NOT IN ( SELECT MID  FROM aidtedmp)
 
 
 SELECT title,COUNT(title) FROM dede_addoninfos GROUP BY title  ORDER BY title  -- 6243
 
 -- 方法三、直接在原表上操作，sql语句如下
 


  DELETE FROM dede_addoninfos WHERE aid NOT IN (SELECT MID FROM (
  SELECT MIN(aid) AS MID  FROM dede_addoninfos GROUP BY title) s)