如何从MySQL的选择结果中删除数据？

Question

这段代码在MySQL 5.0上无法运行，如何改写以使其能够运行

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 ))

我想删除没有唯一id的列。补充一下，大多数情况下只有一个id（我尝试了in语法，也不起作用）。

- IAdapter

Answer 1

SELECT (子)查询返回结果集。因此，在WHERE子句中需要使用IN而不是=。

此外，正如此答案所示，您不能在同一查询内从子查询中修改相同的表。但是，您可以分别使用SELECT和DELETE进行操作，或者嵌套另一个子查询并为内部子查询结果设置别名（看起来有点像hacky）：

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

或者像Mchl建议的那样使用连接查询。

- BoltClock

---

我有一个包含150个重复键的表格。我执行了上述查询，它显示“受影响的行数为144行”，但是仍然存在重复键。所以我再次执行了查询，它显示受影响的行数为5行，然后又变成了1行。最终所有的重复键都被删除了。这是为什么呢？ - Alex

这是因为你只删除了每组重复项中的一个条目：SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ) - havvg

@thang：这就是为什么我建议给内部子查询取一个别名。 - BoltClock

我做了完全相同的事情。你使用了InnoDB吗？我想知道它是否取决于引擎。 - thang

请问一下，“As p”是什么意思？ - Cricketer

Answer 2

DELETE 
  p1
  FROM posts AS p1 
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)

- Mchl

---

这似乎可以工作，但是我对语法感到困惑，并且找不到其他资源来解释它。CROSS JOIN 显然执行笛卡尔积，因此似乎可能会做一些不必要的工作或表现不佳？有人能解释一下吗？ - wintron

只有在没有“USING”子句的情况下，它才会执行笛卡尔积。使用“USING”时，产品仅限于具有相同“id”列值的对，因此实际上非常有限。 - Mchl

你能用内连接做同样的事情吗？即 DELETE p1 FROM posts AS p1 INNER JOIN (SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1) AS p2 ON p2.ID=p1.ID。 - Cave Johnson

@Andrew：是的。从功能上讲，这些连接是完全相同的。 - Mchl

@wintron CROSS JOIN 在 MySQL 中与 [INNER] JOIN 相同；它不是按照 ANSI SQL 描述的方式实现 CROSS JOIN。SELECT ... FROM a JOIN b USING (x) 是 SELECT ... FROM a JOIN b ON b.x = a.x 的语法糖。 - Jivan Pal

Answer 3

你可以使用内连接：

DELETE 
    ps 
FROM 
    posts ps INNER JOIN 
         (SELECT 
           distinct id 
         FROM 
             posts 
         GROUP BY id  
      HAVING COUNT(id) > 1 ) dubids on dubids.id = ps.id  

- Charif DZ

---

这很棒，因为它适用于具有复合主键的查询。 - Archmede

当我在MySQL 5.7中进行测试时，这比BoltClock的答案更快。 - Sangbeom Han

Answer 4

如果你想删除所有重复项，但保留每个重复组中的一个，这是一个解决方案：

DELETE posts
FROM posts
LEFT JOIN (
    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) = 1

    UNION

    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) != 1
) AS duplicate USING (id)
WHERE duplicate.id IS NULL;

- havvg