删除mysql表中重复的记录,如何从MySQL表中删除重复的记录？

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

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

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"

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

Is it possible to solve it in one mysql command?

Thanks.

解决方案

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.