在INNER JOIN中使用COUNT删除MySQL并不删除所有记录

I'm trying to delete records from a table if there are more than 15 from the same company (as in, if there are more than 15 records from a company, all of that companies records are deleted).

如果来自同一家公司的记录超过15个,我试图从表中删除记录(如果公司有超过15条记录,则删除所有公司记录)。

The query I'm using is as follows:

我正在使用的查询如下:

DELETE l.* FROM literature_request l 
INNER JOIN 
    (SELECT literature_request_id 
     FROM literature_request 
     GROUP BY company 
     HAVING COUNT(*) > 15) lr 
ON l.literature_request_id = lr.literature_request_id;

This should delete somewhere around 25,000 rows, but strangely only deletes around 500 each time I run it:

这应该删除大约25,000行,但奇怪的是每次运行它时只删除大约500行:

mysql> DELETE l.* FROM literature_request l INNER JOIN (SELECT literature_request_id FROM literature_request GROUP BY company HAVING COUNT(*) > 15) lr ON l.literature_request_id = lr.literature_request_id;
Query OK, 564 rows affected (0.39 sec)

mysql> DELETE l.* FROM literature_request l INNER JOIN (SELECT literature_request_id FROM literature_request GROUP BY company HAVING COUNT(*) > 15) lr ON l.literature_request_id = lr.literature_request_id;
Query OK, 547 rows affected (2.24 sec)

mysql> DELETE l.* FROM literature_request l INNER JOIN (SELECT literature_request_id FROM literature_request GROUP BY company HAVING COUNT(*) > 15 LIMIT 0,30000) lr ON l.literature_request_id = lr.literature_request_id;
Query OK, 533 rows affected (1.27 sec)

mysql> DELETE l.* FROM literature_request l INNER JOIN (SELECT literature_request_id FROM literature_request GROUP BY company HAVING COUNT(*) > 15 LIMIT 0,30000) lr ON l.literature_request_id = lr.literature_request_id;
Query OK, 523 rows affected (0.43 sec)

I'm not adding new rows in between runs, so I'm wondering why it deletes ~500 each time I run it. Shouldn't it delete all of them the first time, and then not affect any rows with subsequent runs?

我不是在运行之间添加新行,所以我想知道为什么每次运行它都会删除~500。它不应该第一次删除所有这些,然后不影响随后运行的任何行?

Is there an error on my part here, or is there a better way to accomplish what I'm trying to do?

我这里有错误,还是有更好的方法来完成我想做的事情?

1 个解决方案

#1

If I understand correctly, you want to join on the company:

如果我理解正确,你想加入公司:

DELETE l FROM literature_request l 
INNER JOIN 
    (SELECT company 
     FROM literature_request 
     GROUP BY company 
     HAVING COUNT(*) > 15) lr 
ON l.company = lr.company 

The reason behind the weird behaviour is that in the subquery, you were grouping by company but you were selecting the literature_request_id. But which one - of the 15 or more - should the SQL engine be selecting? It was choosing one (more or less randomly), so only that row (1 row from every company) was deleted.

这种奇怪行为背后的原因是,在子查询中,您按公司分组,但是您选择了literature_request_id。但是,SQL引擎应该选择哪一个 - 或者15个或更多?它选择了一个(或多或少随机),因此只删除了那一行(每个公司的一行)。