求一句 MySQL SQL 语句，表内指定字段重复的条目，以 UpdateTime 字段排序，只保留最新一条。。。

select rid from rejectgoods t1
exists (select 1 from rejectgoods where t1. name = t2.name ..... and t1.address = t2. address and t1.updatetime < t2.updatetime )

把查出来的 rid 删了

LGA1150

Aug 31, 2020 via Android

DELETE a
FROM rejectgoods a, rejectgoods b
WHERE a.name = b.name
AND a.size = b.size
AND ... (其他字段省略)
...
AND a.UpdateTime < b.UpdateTime

这样 UpdateTime 较小的就被删除

uti6770werty

Aug 31, 2020

@kiracyan
@LGA1150
感谢两位，字段名称是英文的测试没问题，但是中文的字段名不行，
尽管已经试过格式修改，因为不是很了解 MySQL，临急抱佛脚也不是很懂
WHERE t1.`城市` = t2.`城市`
WHERE a.城市 = b.城市的句式，

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE a.城市 = b.城市

gavindexu

Sep 1, 2020 via iPhone

给中文的字段名前后加上“`”就可以了啊

muskill

Sep 1, 2020

@LGA1150 直接 delete 不好吧😳