删除 SQL 查询结果集 [英] Delete an SQL query result set

问题描述

我正在尝试删除 SQL 结果集，但它不起作用:

I'm trying to delete an SQL result set but it won't work:

DELETE FROM votes
WHERE id IN (
SELECT *
FROM votes v
LEFT JOIN comments c ON f.id = v.post_id
GROUP BY v.id
HAVING COUNT(c.comment) = 0 )

推荐答案

确实，您不能在直接子选择中使用要从中删除行的同一个表，但有一个小技巧 - 在子选择上一个子选择作为派生表 - 你可以做到:

It's true, that you can't use the same table from which you want to delete rows in a direct subselect, but with a little trick - a subselect on a subselect as derived table - you can do it:

DELETE FROM votes
WHERE id IN (
    SELECT 
        t.id
    FROM ( 
        SELECT v.id, COUNT(c.comment) cnt
        FROM votes v
        LEFT JOIN comments c ON f.id = v.post_id
        GROUP BY v.id
        HAVING COUNT(c.comment) = 0 
    ) t
);

我假设应该删除没有注释的行.

I'm assuming that the rows without comments should be deleted.

这篇关于删除 SQL 查询结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！