如何删除重复的行并保留第一行? [英] How do I delete duplicate rows and keep the first row?

查看:228
本文介绍了如何删除重复的行并保留第一行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我犯了一个错误,并且有多余的重复项.

I made a mistake and I have unwanted duplicates.

我有一个包含4个关键字段的表格. A1k1k2k3.

I have a table with 4 key fields. A1, k1, k2, k3.

A1是自动增量键和主键.

A1 is auto increment and the primary key.

k1k2k3的组合应该是唯一的,在创建唯一索引之前,我必须删除重复的行.有些行有一个重复项,有些行有很多重复项.

the combination of k1, k2 and k3 is supposed to be unique and I have to delete the duplicate rows before I create a unique index. Some rows have one duplicate, some have many.

SELECT CONCAT(k1, k2, k) AS dup_value
  FROM myviews
 GROUP BY dup_value
HAVING (COUNT(dup_value) > 1)

向我显示了我需要处理的重复值.但是现在我不知道该如何保留一个并删除每个重复集的其余部分.

shows me duplicates values that I need to deal with. But now I don't know how to keep one and delete the rest of each duplicate set.

推荐答案

备份数据,然后...

MySQL支持DELETE语句中的JOIN .如果要保留第一个重复项:

Backup your data, then...

MySQL supports JOINs in DELETE statements. If you want to keep the first of the duplicates:

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MIN(t.a1) AS min_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.min_a1 != a.a1

如果要保留最后一个重复项:

If you want to keep the last of the duplicates:

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MAX(t.a1) AS max_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.max_a1 != a.a1

这篇关于如何删除重复的行并保留第一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆