一个表中最常出现在另一表中的 MYSQL 条目

Question

我有 2 个表、作者和书籍

authors 包含唯一的 IDauthorId

书籍也包含此作为外键

我需要知道书籍数量最多的作者。如果 2 个或更多作者并列最多书籍，我需要显示这两位作者

我已经能够通过首先获得最大计数来实现这一点

SELECT @maxCount := (MAX(counter)) FROM (SELECT count(*) AS counter FROM books GROUP BY authorId) AS counts;

然后用它来获取包含该计数的 Id，作为我的作者选择的一部分

SELECT * 
FROM authors 
WHERE authorId IN (
    SELECT authorId 
    FROM books 
    GROUP BY authorId 
    HAVING COUNT(*) = @maxCount
);

有人告诉我，我不允许使用变量，而且如果表变得非常大，我所做的事情效率会非常低。

我在这里遗漏了一些明显的东西吗？有没有办法在没有变量(或临时表)的情况下在单个语句中执行此操作，并且不必两次选择/分组整个书籍表？

Answer 1

SELECT author, COUNT(*)
FROM authors
JOIN books
ON authors.authorId=books.AuthorId
GROUP BY author
ORDER BY COUNT(*) DESC

将为您提供一个按每位作者的书籍数量排序的列表。我附近没有要测试的实例，并且倾向于避开嵌入变量，但期望类似......

SELECT *
FROM (
  SELECT author
  , @maxcount:=IF(COUNT(*)>@maxcount,COUNT(*), @maxcount) 
  , COUNT(*) AS cnt
  FROM authors
  JOIN books
  ON authors.authorId=books.AuthorId
  GROUP BY author
  ORDER BY COUNT(*) DESC
) ilv
WHERE cnt=@maxcount;

对于大型数据集(即使使用正确的索引)，性能仍然很差。如果您必须经常对超过 100,000 条记录运行此查询，那么您可能会考虑对数据进行非规范化。