在 MySQL 中从 HAVING COUNT(*) 中删除

2021-12-30 00:00:00 count mysql sql-delete having

好的，这里已经有几篇关于此的帖子，而网络上的帖子则更少.我真的尝试了其中的每一个，但无法正常工作.希望这里有人可以可怜我:)

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)

我找到的最接近的代码如下所示.语法检查正常并运行，但它似乎只是挂断了数据库.我已经让它运行了长达 10 分钟，但什么也没发生，所以我中止了它.

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.

DELETE
FROM ag_master
WHERE part_id IN (
  SELECT part_id
  FROM ag_master
  GROUP BY part_desc
  HAVING COUNT(*) > 1000
);

这是tmp表上的解释计划

Here's the explain plan on the tmp table

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)

推荐答案

如手册中所述:

目前，您不能从表中删除并从子查询中的同一个表中选择.

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;

相关文章