MySQL 基于连接表限制结果

Question

我有 2 个表，但以多对多关系链接，因此有 3 个表:

Table Author :
idAuthor,
Name 

+----------+-------+
| idAuthor | Name  |
+----------+-------+
|        1 | Renee |
|        2 | John  |
|        3 | Bob   |
|        4 | Bryan |
+----------+-------+
Table Publication:
idPublication,
Title,
Type,
Date,
Journal,
Conference

+---------------+--------------+------+-------------+------------+-----------+
| idPublication | Title        | Date | Type        | Conference | Journal   |
+---------------+--------------+------+-------------+------------+-----------+
|             1 | Flower thing | 2008 | book        | NULL       | NULL      |
|             2 | Bees         | 2009 | article     | NULL       | Le Monde  |
|             3 | Wasps        | 2010 | inproceding | KDD        | NULL      |
|             4 | Whales       | 2010 | inproceding | DPC        | NULL      |
|             5 | Lyon         | 2011 | article     | NULL       | Le Figaro |
|             6 | Plants       | 2012 | book        | NULL       | NULL      |
+---------------+--------------+------+-------------+------------+-----------+
Table author_has_publication :
Author_idAuthor,
Publication_idPublication

+-----------------+---------------------------+
| Author_idAuthor | Publication_idPublication |
+-----------------+---------------------------+
|               1 |                         1 |
|               2 |                         2 |
|               3 |                         3 |
|               4 |                         4 |
|               1 |                         5 |
|               2 |                         5 |
|               3 |                         5 |
|               3 |                         6 |
+-----------------+---------------------------+

我想做的是让排名前 X 的作者拥有最多的出版物。

我使用以下请求获得了具有最多出​​版物的 idAuthor 的结果:SELECT Author_idAuthor, COUNT(*) as count FROM Author_has_publication GROUP BY Author_idAuthor ORDER BY count DESC;我得到了作者 ID 的列表，按出版物数量排序:

+-----------------+-------+
| Author_idAuthor | count |
+-----------------+-------+
|               3 |     3 |
|               2 |     2 |
|               1 |     2 |
|               4 |     1 |
+-----------------+-------+

但是当我尝试选择与上一个查询的结果集的前 X 相对应的作者时，出现错误我正在尝试此 SELECT TOP 2 FROM 作者 WHERE (SELECT Author_idAuthor, COUNT(*) as count FROM Author_has_publication GROUP BY Author_idAuthor ORDER BY count DESC)=idAuthor;我认为这可能是因为我的内部查询返回 2 行，并且我在这里做了一个简单的 SELECT ，或者我需要一个 JOIN ，但我不知道如何在这里使用它.

Answer 1

MySQL 没有 TOP 关键字。但它确实有一个 LIMIT 关键字。无论如何，您的查询都是无效的。

这里有几个选项。以下是相关子查询的示例:https://en.wikipedia.org/wiki/Correlated_subquery

SELECT 
    a.idAuthor, 
    a.Name , 
    (SELECT COUNT(*) from author_has_publication ahp WHERE   
        ahp.Author_idAuthor = a.idAuthor) AS publication_count
FROM 
    author a
ORDER BY 
    publication_count DESC
LIMIT 2

正如引用的文章所述，上述方法效率低下，因为需要对结果的每一行重新执行子查询。如果您实际上不需要结果集中的计数，那么下面的代码会更有效，因为子查询是不相关的并且仅执行一次。

SELECT 
    a.idAuthor, 
    a.Name 
FROM 
    author a
INNER JOIN 
    (select ahp.Author_idAuthor AS idAuthor, COUNT(*) as publication_count 
        FROM author_has_publication ahp GROUP BY ahp.Author_idAuthor LIMIT 2) 
        AS TEMP ON TEMP.idAuthor = a.idAuthor