mysql – 行使GROUP BY删除一再项的查询

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 <> d.id_specific_price;

功效：

| ID_SPECIFIC_PRICE | ID_PRODUCT |
|-------------------|------------|
|                 3 |          2 |
|                 7 |          3 |

这是SQLFiddle演示

（编辑：湖南网）

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