MySQL数据去掉重复项

去掉qiyeminlu.qiyemingdan表中 name名字相同的项,并且保留id最小的一个

FROM qiyeminlu.qiyemingdan
WHERE name in(
select 
name from  (SELECT name FROM  qiyeminlu.qiyemingdan 
GROUP BY name 
HAVING COUNT( * ) >1) as temp )and id not in (
select 
id from  (SELECT min(id) as id FROM  qiyeminlu.qiyemingdan 
GROUP BY name 
HAVING COUNT( * ) >1)as temp);

注意如果出现执行错误。可能是mysql 的安全update模式导致的sale update，这个时候执行：

SET SQL_SAFE_UPDATES = 0;

删除huanghe 表中phone的重复项

DELETE FROM huanghe WHERE id IN (SELECT id FROM (
SELECT t1.id
FROM huanghe t1, (
SELECT phone, MIN(id) AS minid FROM huanghe
GROUP BY phone HAVING COUNT(phone) > 1
)t2
WHERE t1.phone = t2.phone AND t1.id = t2.minid

)t3)

这个需要多次执行，因为每次只能删除一个