mysql 删除重复记录只剩下一条

最新推荐文章于 2020-09-09 17:21:07 发布

weixin_34019929

最新推荐文章于 2020-09-09 17:21:07 发布

阅读量101

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/dslcode/blog/1591210

版权

2019独角兽企业重金招聘Python工程师标准>>>

1.创建临时表，把需要删除的id放入
2.删除记录
3.删除临时表

最重要的：
筛选出需要删除的id
(1)记录分组并且count>1，选出所有重复的记录字段
   select user_id from user_get_prise_count GROUP BY user_id HAVING count(user_id)>1
(2)记录分组并且count>1，选出需要保留的一列id
   select min(id) from user_get_prise_count GROUP BY user_id HAVING count(user_id)>1
(3)需要删除的记录字段放入临时表
   select id from user_get_prise_count where user_id in (1) and id!=(2)

sql:
create table tmp as (
select id from user_get_prise_count where user_id in
(select user_id from user_get_prise_count GROUP BY user_id HAVING count(user_id)>1)
and id!=(select min(id) from user_get_prise_count GROUP BY user_id HAVING count(user_id)>1)
);
delete from user_get_prise_count where id in(select * from tmp);
drop table tmp;

转载于:https://my.oschina.net/dslcode/blog/1591210