如何从MySQL表中删除重复的记录?(复制)

This question already has an answer here:

这个问题已经有了答案:

Finding duplicate values in MySQL 21 answers
在MySQL 21中找到重复的值

I would be grateful if you could help me to solve this sql problem.

如果您能帮助我解决这个sql问题，我将不胜感激。

I have a table that contains two columns. Name them UserID and LocationID in this example.

我有一个包含两列的表。在本例中，将它们命名为UserID和LocationID。

I would like to clean this table, so I would like to keep the first occurences of each unique pairs of UserIDs and LocationIDs and delete additional rows that contains the same pairs. In other words I would like to know every location for each user where he has been to. If he visited the same place again and again and it was logged, I would like to delete those records. For example, this is the original table:

我希望清理这个表，因此我希望保留每个唯一的UserIDs和locationid对的第一次出现，并删除包含相同对的其他行。换句话说，我想知道每个用户去过的每个位置。如果他一次又一次地访问同一个地方，并且记录下来，我想删除这些记录。例如，这是原始表格:

ID UserID  LocationID
1. "user1" "location1"
2. "user1" "location2"
3. "user2" "location3"
4. "user1" "location1"
5. "user2" "location3"
6. "user2" "location3"

I would like to remove the 4., 5. and 6. rows.

我想去掉4。5。和6。行。

Is it possible to solve it in one mysql command? Thanks.

可以用一个mysql命令解决吗?谢谢。

2 个解决方案

#1

Delete using a self-join:

删除使用自连接:

DELETE t2.*
FROM tablename AS t1
INNER JOIN tablename AS t2
    ON t1.UserID = t2.UserID
    AND t1.LocationID = t2.LocationID
WHERE t1.ID < t2.ID

tablename should be replace with the name of your table.

应该用表的名称替换tablename。

#2

To add on to Oswald's answer, to prevent duplicates in the future you can use INSERT...ON DUPLICATE UPDATE:

为了补充Oswald的答案，为了防止将来出现重复，您可以使用INSERT……在重复的更新:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

#1