如何从MySQL表中删除重复的记录? [英] How to remove duplicated records from a MySQL table?
问题描述
如果能帮助我解决此sql问题,我将不胜感激.
I would be grateful if you could help me to solve this sql problem.
我有一个包含两列的表.在此示例中,将它们命名为UserID和LocationID.
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:
ID UserID LocationID
1. "user1" "location1"
2. "user1" "location2"
3. "user2" "location3"
4. "user1" "location1"
5. "user2" "location3"
6. "user2" "location3"
我想删除4.,5和6.行.
I would like to remove the 4., 5. and 6. rows.
是否可以在一个mysql命令中解决它? 谢谢.
Is it possible to solve it in one mysql command? Thanks.
推荐答案
使用自连接删除:
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
应该用表名替换.
这篇关于如何从MySQL表中删除重复的记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!