我有一个表格MYSQL
它包含以下字段:
issue_id int(11),
label_id int(11)
在此表中,如果计数为 2,我想根据某个字段的计数删除行,否则不要删除它
DELETE FROM issue_labels
INNER JOIN (
SELECT count(*) c,
`issue_id`
FROM `issue_labels`
GROUP BY `issue_id`
) temp
ON issue_labels.issue_id = temp.issue_id
WHERE c = 2
上面的SQL抛出了一个错误,但是如果我这样做:
SELECT *
FROM issue_labels
INNER JOIN (
SELECT count(*) c,
`issue_id`
FROM `issue_labels`
GROUP BY `issue_id`) temp
ON issue_labels.issue_id = temp.issue_id
WHERE c = 2
它有效!我想说的是,连接有效,但删除命令无效。
我也尝试使用子查询,例如:
DELETE FROM issue_labels
WHERE issue_id IN (
SELECT `issue_id`
FROM `issue_labels`
GROUP BY `issue_id`
HAVING count(*) = 2
)
但这抛出了一个错误,消息是:
"#1093 - 无法在 FROM 子句中指定要更新的目标表'issue_labels' ">
此外,我更喜欢使用连接而不是嵌套的解决方案,因为我相信 join 比子查询更有效。
试试这个:
DELETE issue_labels
FROM issue_labels
INNER JOIN (
SELECT count(*) c,
`issue_id`
FROM `issue_labels`
GROUP BY `issue_id`
) temp
ON issue_labels.issue_id = temp.issue_id
WHERE c = 2
解释:
联接迫使您使用"从issue_labels中删除issue_labels"而不是正常的"从issue_labels中删除",因为要删除的表不再是明确的。