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

问题描述：

如果能帮助我解决此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表中删除重复的记录?

相关推荐