我有张桌子:
PRICE_UPDATE
id (int 5, auto-increment, primary, unique)
part_number (varchar 10, non-null)
price (float(10,2), non-null)一些part_number是重复的(一个或多个重复记录)。有时以相同的价格,有时以不同的价格。
如何根据part_number删除所有重复行,如果价格相同,则只留下最高价格或仅1条记录?
这在直MySQL中甚至可以实现吗?
发布于 2012-01-18 16:49:38
DELETE t1
FROM YourTable t1, YourTable t2
WHERE t1.part_number = t2.part_number
AND (t1.price, t1.id) < (t2.price, t2.id)发布于 2012-01-18 16:50:27
从内到外:
part_number
中。
delete tablename where id not in (
(select max(id) from tablename a
inner join
( select id, max(price)
from tablename
group by part_number ) b on a.id = b.id and a.price = b.price
group by part_number))https://stackoverflow.com/questions/8913917
复制相似问题