【Mysql】有关数据表中存在重复数据问题解决方案（已实现mysql类型的sql）

还算善良_

已于 2024-04-26 14:03:31 修改

阅读量142

点赞数 3

文章标签： mysql sql 数据库

于 2024-04-26 13:56:44 首次发布

本文链接：https://blog.csdn.net/weixin_46522411/article/details/138216845

版权

本文介绍了两种SQL解决方案：方案1利用窗口函数ROW_NUMBER()找出并删除table_name中的重复行，方案2则是通过查找每个组合(INVOICE_NO,ORDER_NO)的最大ID来识别并移除重复项。

摘要由CSDN通过智能技术生成

直接看sql

1、方案1

# 查询哪些数据有重复
SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY invoice_no, order_no ORDER BY id) as row_num
  FROM table_name
) o
WHERE o.row_num > 1;

#删除重复数据（保留一条）
DELETE FROM table_name  
WHERE id IN (  
    SELECT id  
    FROM (  
        SELECT id,  
               ROW_NUMBER() OVER (PARTITION BY invoice_no, order_no ORDER BY id) as rn  
        FROM table_name  
    ) t  
    WHERE t.rn > 1  
);

2、方案2


SELECT *
FROM
table_name a
WHERE
a.id IN  (  select  b.id from ( SELECT MAX( c.id ) id FROM table_name c  GROUP BY invoice_no, order_no HAVING count( invoice_no ) > 1 ) b);

delete
FROM
table_name a
WHERE
a.id IN  (  select  b.id from ( SELECT MAX( c.id ) id FROM table_name c  GROUP BY invoice_no, order_no HAVING count( invoice_no ) > 1 ) b);