mysql - 从表中删除重复行

Question

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 2    22.92138145   72.44103413     13.96 km/h     2011-09-26  National, Gujarat, India
 3    22.92138134   72.44103423     15.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad
 6    22.92138484   72.44103293     19.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

我想编写一个查询，使我的结果如下所示:

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

我想根据地址去掉重复的行。

Answer 1

检查要删除的内容:

SELECT distinct t1.*
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

如果您对此感到满意:

DELETE t1
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

这样您就可以在 id 列上保留最大值的记录