5.删除表中重复的数据

1.需求

删除评论表中对同一订单同一商品的重复评论，只保存最早的一条

1.步骤

• 查看是否存在于同一订单同一商品的重复评论
• 备份prodect_comment表
• 删除同一订单的重复评论

//1.查找重复的数据
select order_id , product_id ,count(*)
from product_comment
group by order_id ,product_id
having count(*)>1;

//2.备份数据表
create table bak_product_comment_171022
as
select * from product_comment;

或者

create table bak_product_comment_171022
like product_comment

inset into bak_product_comment_171022 select * from product_comment;

//3.删除重复数据
delete a from product_comment a
join (
    select order_id,product_id,MIN(comment_id)as comment_id
    from product_comment
    group by order_id,product_id 
    having count(*) >= 2
) b on a.order_id=b.order_id 
    and a.product_id=b.product_id  
    and a.comment_id > b.comment_id;