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

Question

我有2张桌子，有作者和书

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

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

我被告知不允许使用变量，并且如果表变得很大，我所做的工作效率会非常低。

我是不是漏掉了什么明显的东西？有没有一种方法可以在一个语句中实现这一点，而不需要变量（或临时表），并且不必对整个books表进行两次选择/分组？

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条记录来运行此查询，那么您可能会考虑对数据进行反常态化处理。