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;