mysql去重，只留一个（id最小的）

今天爬了一堆数据，结果发现有些重复的，无奈之下只好写个sql语句把mysql里去重了。如果大家有一样的需求可以直接拿去改吧改吧用。

• 表名：你自己数据库的表名
• 字段名：根据什么字段去重
• id：一般默认就叫“id”，特殊的自己改掉下面的id就好
• delete from 表名 where 字段名 in (select 字段名from (select 字段名 from 表名 group by 字段名 having count(字段名)>1)a) and id not in (select id from(select min(id) as id from 表名 group by 字段名 having count(字段名)>1)b)

删除重复壁纸信息，只保留id最小的那个
delete from tb_wallpaper where wurl_preview in (select wurl_preview from (select wurl_preview from tb_wallpaper group by wurl_preview having count(wurl_preview)>1)a) and id not in (select id from(select min(id) as id from tb_wallpaper group by wurl_preview having count(wurl_preview)>1)b)

以名称来删除
delete from tb_wallpaper where wname in (select wname from (select wname from tb_wallpaper group by wname having count(wname)>1)a) and id not in (select id from(select min(id) as id from tb_wallpaper group by wname having count(wname)>1)b);

删除重复类别信息，只保留id最小的那个
delete from tb_wallpapercategory where cname in (select cname from (select cname from tb_wallpapercategory group by cname having count(cname)>1)a) and id not in (select id from(select min(id) as id from tb_wallpapercategory group by cname having count(cname)>1)b)

删除包含Free的数据
delete from tb_wallpaper where wurl_preview like ‘%Free%’;