使用三个表格 (mysql) 打印每个最大值

Question

我需要找到在我的数据库中写卷最多的作者，我有三个表"卷"，"作者"，"作者"。表卷具有标题、volume_id(主键(、年份、edition_year等列。表 AUTHOR 具有用于通用信息的列，如姓名、姓氏、ID 等，表 WRITE BY 用于连接 AUTHOR 和 VOLUMES，它包含列volume_id和author_id。 我的数据可以包含相同卷的许多副本，因此我想我需要对同一卷的每个副本进行分组。

我写的查询是：

SELECT A.NAME, A.SURNAME, COUNT(V.ID) no_of_volumes
FROM VOLUMES AS V, AUTHOR AS A JOIN WRITTEN_BY W
WHERE (V.ID = W.VOLUME_ID AND A.ID = W.A_ID)
GROUP BY A.NAME, A.SURNAME
ORDER BY no_of_volumes DESC
LIMIT 1;

现在，这应该只打印一个作者，但我希望它打印每个写了相同卷数的作者......我该怎么做？

Answer 1

如果您被允许稍微更改方案(使用唯一的 ID 名称(，那么这可以解决问题

select AUTHOR.NAME
,   count(*) VolumeCount
from AUTHOR
inner join WRITTEN_BY using(AUTHOR_ID)
inner join VOLUME using(VOLUME_ID)
group by a.AUTHOR_ID
order by VolumeCount desc
limit 1;

x 如果不是，这应该可以解决问题：

select AUTHOR.NAME
,   count(*) VolumeCount
from AUTHOR
inner join WRITTEN_BY on AUTHOR_ID = AUTHOR.ID
inner join VOLUME on VOLUME_ID = VOLUME.ID
group by a.AUTHOR_ID
order by VolumeCount desc
limit 1;

请保留仅用于过滤的位置。将其用于外键会使代码变得混乱。 (如果您的表和字段名称是大写的，则使用小写关键字是有意义的(

Answer 2

您必须在written_by内聚合两次：一次用于获取写入的最大卷数，然后获取写入最大卷数的作者 ID。
然后加入authors以获取作者详细信息：

SELECT a.*, t.no_of_volumes
FROM author a INNER JOIN (
SELECT author_id, COUNT(*) no_of_volumes
FROM written_by
GROUP BY author_id
HAVING COUNT(*) = (
SELECT COUNT(*) no_of_volumes
FROM written_by
GROUP BY author_id
ORDER BY no_of_volumes DESC LIMIT 1
)
) t ON t.author_id = a.id

不需要表volumes。

对于MySql 8.0+，可以使用窗口函数RANK()来简化代码：

SELECT a.*, t.no_of_volumes
FROM author a INNER JOIN (
SELECT r.*
FROM (
SELECT author_id, COUNT(*) no_of_volumes, 
RANK() OVER (ORDER BY COUNT(*) DESC) rnk
FROM written_by
GROUP BY author_id
) r
WHERE r.rnk = 1
) t ON t.author_id = a.id