id_specific_price id_product
-------------------------------
1 2
2 2
3 2
4 3
5 3
6 3
7 3
需要删除重复项,预期结果：
id_specific_price id_product
-------------------------------
3 2
7 3
SELECT *
FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(id_specific_price)
FROM ps_specific_price
GROUP BY id_product)
工作,但
DELETE FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(id_specific_price)
FROM ps_specific_price
GROUP BY id_product)
才不是.有很多例子可以解决这个问题,但由于某种原因,我无法适应它.我相信它是GROUP BY.例如：
DELETE FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(p.id_specific_price)
FROM (SELECT * FROM ps_specific_price ) as p)
GROUP BY id_product
我在哪里错了？
最佳答案
如果您正在寻找MysqL的解决方案,那么您可以使用正确的multi table
原文链接：https://www.f2er.com/mysql/433684.htmlDELETE语法以及这样的JOIN
DELETE p
FROM ps_specific_price p JOIN
(
SELECT id_product,MAX(id_specific_price) id_specific_price
FROM ps_specific_price
GROUP BY id_product
) d
ON p.id_product = d.id_product
AND p.id_specific_price <> d.id_specific_price;
结果：
| ID_SPECIFIC_PRICE | ID_PRODUCT | |-------------------|------------| | 3 | 2 | | 7 | 3 |
这是SQLFiddle演示