无法删除具有GROUP BY和HAVING子句的语句

Question

我试图通过下面的SQL语句删除一些记录，但得到错误#1064-您的SQL语法中有错误；查看与您的MySQL服务器版本相对应的手册，了解在第12行“group BY tsa_.task_attachment_id h”附近使用的正确语法：

有没有人知道我怎么解决错误？

DELETE
    ta
FROM `tasks_recursive` AS tr
INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
INNER JOIN (
    `tasks_attachments` AS tsa_
    INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
) ON tsa_.task_attachment_id = tsa.task_attachment_id
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
GROUP BY
    tsa_.task_attachment_id
HAVING
    COUNT( DISTINCT( tr_.task_id ) ) = 1

我还尝试了下面的句子，在那里我得到了这个错误#1064-您的SQL语法中有一个错误；查看与您的MySQL服务器版本相对应的手册，了解在“group BY tsa_.task_attachment_id HAVING COUNT（DISTINCT(tr_.task_id'at line 12)附近使用的正确语法：

DELETE
    ta
FROM `tasks_recursive` AS tr
INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
INNER JOIN (
    `tasks_attachments` AS tsa_
    INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
) ON tsa_.task_attachment_id = tsa.task_attachment_id
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
GROUP BY
    tsa_.task_attachment_id
HAVING
    COUNT( DISTINCT( tr_.task_id ) ) = 1

上面的句子在many_to_manytasks_attachments表中查找nobody ecxept本身未使用的task_attachments行，该表将从属task_attachments表链接到主tasks_recursive表。

为了解决上述问题，我尝试包装上面的查询但我得到以下错误#1093-您不能在FROM子句中指定目标表“TA_”以进行更新：

DELETE ta_
                FROM `task_attachments` AS ta_
                WHERE ta_.id IN
(
                    SELECT
                        ta.id
                    FROM `tasks_recursive` AS tr
                    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
                    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
                    INNER JOIN (
                        `tasks_attachments` AS tsa_
                        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
                    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
                    WHERE
                        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
                    GROUP BY
                        tsa_.task_attachment_id
                    HAVING
                        COUNT( DISTINCT( tr_.task_id ) ) = 1
                )

最后尝试了双重包装，但也不起作用，这是错误#1064-您的SQL语法中有一个错误；查看与您的MySQL服务器版本相对应的手册，了解第25行“as sub）”附近使用的正确语法：

DELETE
FROM `task_attachments`
WHERE `task_attachments`.id IN
(
SELECT
ta_.id
FROM `task_attachments` AS ta_
WHERE ta_.id IN 
(
    SELECT
        ta.id AS taId
    FROM `tasks_recursive` AS tr
    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
    INNER JOIN (
        `tasks_attachments` AS tsa_
        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
    WHERE
        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    GROUP BY
        tsa_.task_attachment_id
    HAVING
        COUNT( DISTINCT( tr_.task_id ) ) = 1
    ) AS sub
)

Answer 1

试试看

DELETE
FROM `task_attachments`
WHERE `task_attachments`.id IN
(
SELECT
taId
FROM 
(
    SELECT
        ta.id AS taId
    FROM `tasks_recursive` AS tr
    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
    INNER JOIN (
        `tasks_attachments` AS tsa_
        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
    WHERE
        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    GROUP BY
        tsa_.task_attachment_id
    HAVING
        COUNT( DISTINCT( tr_.task_id ) ) = 1
    ) AS sub
)

Answer 2

没错，MySQL的DELETE支持从SELECT语法中借用的几个子句，如JOIN和LIMIT，但不支持所有子句。不支持GROUP BY和HAVING。

当我读到你的问题时，它似乎比你所做的要简单。您确实希望删除只属于一个task_id的task_attachments。并且对文件名和创建者ID有一些条件。

您可以为此使用排除联接。也就是说，尝试连接到引用相同附件id的不同任务，如果没有找到，则外部连接将返回NULL。然后在WHERE子句中测试NULL，当这种情况发生时，您发现了一个仅由一个task_id引用的附件。

DELETE 
    ta
FROM task_attachments AS ta
INNER JOIN tasks_attachments AS tsa1 
    ON tsa1.task_attachment_id = ta.id
LEFT OUTER JOIN tasks_attachments AS tsa2 
    ON tsa1.task_attachment_id = tsa2.task_attachment_id
    AND tsa1.task_id <> tsa2.task_id 
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    AND ta.creator_id = 279
    AND tsa2.task_attachment_id IS NULL;

我认为在这个查询中没有引用task_recursive的必要，因为task_id已经存在于tasks_attachments中。

在你删除任何东西之前，它当然会更好的尝试作为一个选择第一，以双重检查我的重新制定给你你需要的。

SELECT 
    ta.*
FROM task_attachments AS ta
INNER JOIN tasks_attachments AS tsa1 
    ON tsa1.task_attachment_id = ta.id
LEFT OUTER JOIN tasks_attachments AS tsa2 
    ON tsa1.task_attachment_id = tsa2.task_attachment_id
    AND tsa1.task_id <> tsa2.task_id 
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    AND ta.creator_id = 279
    AND tsa2.task_attachment_id IS NULL;