sql 删除重复数据
2018-07-26 本文已影响0人
巡山的小猴子
第一步查出重复数据
select id from phone_blacklist where (userid,phone) in
(
select userid,phone from phone_blacklist
group by userid,phone having count(*) > 1
)
第二步排除不想删除的第一条
select id from phone_blacklist where (userid,phone) in
(
select userid,phone from phone_blacklist
group by userid,phone having count(*) > 1
)
and id not in
(
select min(id) from phone_blacklist
group by userid,phone having count(*) > 1
)
第三步开始删
delete from phone_blacklist where id in
(
select id from
(
select id from phone_blacklist where (userid,phone) in
(
select userid,phone from phone_blacklist
group by userid,phone having count(*) > 1
)
and id not in
(
select min(id) from phone_blacklist
group by userid,phone having count(*) > 1
)
) as a
);