如何删除重复的行并保留第一行? [英] How do I delete duplicate rows and keep the first row?
问题描述
我犯了一个错误,并且有多余的重复项.
I made a mistake and I have unwanted duplicates.
我有一个包含4个关键字段的表格. A1
,k1
,k2
,k3
.
I have a table with 4 key fields. A1
, k1
, k2
, k3
.
A1
是自动增量键和主键.
A1
is auto increment and the primary key.
k1
,k2
和k3
的组合应该是唯一的,在创建唯一索引之前,我必须删除重复的行.有些行有一个重复项,有些行有很多重复项.
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屋!