mysql - 删除在一列中具有重复值但在另一列中具有不同值的行

Question

我在 MariaDB 5.5 中有一个表，如下所示:

| PRODUCT_ID | STORE_ID |
-------------------------
|        111 |        0 |
|        111 |        1 |
|        112 |        0 |
|        112 |        1 |
|        113 |        0 |
|        114 |        1 |
|        115 |        1 |
|        116 |        0 |
|        117 |        0 |
|        117 |        1 |

我想要做的是删除列 product_id 的所有重复行，其中 store_id 的值为 0，因此最终表将如下所示:

| PRODUCT_ID | STORE_ID |
-------------------------
|        111 |        1 |
|        112 |        1 |
|        113 |        0 |
|        114 |        1 |
|        115 |        1 |
|        116 |        0 |
|        117 |        1 |

此 sql 查询按列 product_id 返回所有重复条目:

SELECT `product_id` FROM `table` GROUP BY `product_id` HAVING COUNT(*) > 1

所以我尝试了这个:

DELETE FROM `table` AS a WHERE a.`store_id` = '0' AND a.`product_id` IN (SELECT b.`product_id` FROM  `table` AS b GROUP BY b.`product_id` HAVING COUNT(*) > 1)

但是它给我一个 sql 语法错误:

#1064 - 你的 SQL 语法有错误；查看与您的 MariaDB 服务器版本对应的手册，了解在第 1 行的 'AS a WHERE a.`store_id` = '0' AND a.`product_id` IN (SELECT b.`product_id` FROM ' 附近使用的正确语法

此错误的原因是什么？我应该改用什么查询？

Answer 1

此查询将删除所有 STORE_ID=0 且重复的行:

DELETE t1.*
FROM
  yourtable t1 INNER JOIN (SELECT PRODUCT_ID
                           FROM yourtable
                           GROUP BY PRODUCT_ID
                           HAVING COUNT(*)>1) t2
  ON t1.PRODUCT_ID = t2.PRODUCT_ID
     AND t1.STORE_ID=0;

请参阅 fiddle here .