删除重复的旧行并仅保留最新的mysql [英] Delete duplicated old rows and keep only latest mysql

问题描述

我有一个表

id:name:passportnumber:score
1:xxx:123456:99
2:yyy:789012:88
3:xxx:123456:11
4:xxx:123456:44
5:xxx:123456:66
6:xxx:123456:77

我要删除重复的旧列，最新的护照号码相同

I want to delete duplicated old rows and keep only latest one which has same passport number

id:name:passportnumber:score    
2:yyy:789012:88
6:xxx:123456:77

无临时表

感谢

推荐答案

请自我加入您的表格并执行删除：

You should be able to do a self-join to your table and perform the delete:

delete t1
from yt t1
inner join yt t2
  on t1.passportnumber = t2.passportnumber
where t1.id < t2.id;

查看 SQL Fiddle with Demo

这篇关于删除重复的旧行并仅保留最新的mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！