在 MySQL 中从 HAVING COUNT(*) 中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10554627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DELETE FROM HAVING COUNT(*) in MySQL
提问by RFQ Master
Ok so there are couple posts here already on this and fewer still out on the web. I've literally tried every one of them and can not get anything to work. Hopefully someone here can take pity on me :)
好的，所以这里已经有几篇文章了，而网络上的文章更少。我真的尝试了其中的每一个，但无法正常工作。希望这里有人可以可怜我:)
Here is the data I'm working with. I want to delete all these records.
这是我正在处理的数据。我想删除所有这些记录。
SELECT
part_desc, count(*) as rec_num
FROM ag_master
GROUP BY part_desc HAVING COUNT(*) > 1000;
+--------------------------------------+---------+
| part_desc | rec_num |
+--------------------------------------+---------+
| SILICON DELAY LINE, TRUE OUTPUT | 1092 |
| LOADABLE PLD | 1401 |
| 8-BIT, FLASH, 8 MHz, MICROCONTROLLER | 1411 |
| FPGA | 1997 |
| 8-BIT, MROM, 8 MHz, MICROCONTROLLER | 3425 |
+--------------------------------------+---------+
5 rows in set (0.00 sec)
The closest I've come to finding code that would do it is shown below. The syntax checks ok and it runs, however it just seems to hang the database up. I've let it run for as long as 10 minutes and nothing ever happens so I abort it.
我找到的最接近的代码如下所示。语法检查正常并运行，但它似乎只是挂断了数据库。我让它运行了长达 10 分钟，但什么也没发生，所以我中止了它。
DELETE
FROM ag_master
WHERE part_id IN (
SELECT part_id
FROM ag_master
GROUP BY part_desc
HAVING COUNT(*) > 1000
);
Here's the explain plan on the tmp table
这是 tmp 表上的解释计划
mysql> EXPLAIN SELECT * FROM ag_master WHERE part_desc IN (SELECT part_desc FROM tmp);
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | ag_master | ALL | NULL | NULL | NULL | NULL | 177266 | Using where |
| 2 | DEPENDENT SUBQUERY | tmp | system | NULL | NULL | NULL | NULL | 1 | |
+----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
回答by eggyal
As stated in the manual:
Currently, you cannot delete from a table and select from the same table in a subquery.
目前，您不能在子查询中从表中删除和从同一表中选择。
I think you'll have to perform this operation via a temporary table:
我认为您必须通过临时表执行此操作：
CREATE TEMPORARY TABLE temp
SELECT part_desc
FROM ag_master
GROUP BY part_desc
HAVING COUNT(*) > 1000;
DELETE FROM ag_master WHERE part_desc IN (SELECT part_desc FROM temp);
DROP TEMPORARY TABLE temp;
回答by Jonathan Heijmans
Another option is using an inner join to filter the result:
另一种选择是使用内部联接来过滤结果：
DELETE
ag_master.*
FROM
ag_master
INNER JOIN
(
SELECT
part_id
FROM
ag_master
GROUP BY
part_desc
HAVING COUNT(*) > 1000
)AS todelete ON
todelete.part_id = ag_master.part_id
