mysql C 使用GROUP BY删除重复项的查询

发布时间：2023-12-20 07:12:03 所属栏目：MySql 来源：DaWei

导读： id_specific_price id_product
-------------------------------
1 2
2 2
3 2

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 DELETE语法以及这样的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

（编辑：台州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!