MySql 查询问题 子句的意外排序。(靠近"having"位置 117)

Question

我必须为我的大学项目将所有SQL查询转换为MySql，但我遇到了以下查询的问题：

select a.actor_name, count(fa.actor_id) as Movies
from Actor a
join Film_Actor fa on a.actor_id = fa.actor_id
having count(fa.actor_id) = (select distinct max(count(actor_id)) from Film_Actor group by actor_id)
group by a.actor_name;

它说：

意外的子句排序。(接近"具有"在位置117处(

我确信我在MySql代码中有语法问题(我们被告知没有MySql，只有Sql(

Answer 1

不能以这种方式嵌套聚合函数。请尝试这样做：我们使用子查询，按计数排序，并将结果限制为只有1条记录，以获得"；大多数"；电影中有一位演员参演过。

SELECT a.actor_name, count(fa.actor_id) as Movies
FROM Actor a
INNER JOIN  Film_Actor fa on a.actor_id = fa.actor_id
HAVING count(fa.actor_id) = (SELECT count(actor_id) 
FROM Film_Actor 
GROUP BY actor_id 
ORDER BY count(actor_id) DESC
LIMIT 1)
GROUP BYa.actor_name;

部分演示：https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c511932c7a16f8c9f5ecb85f03ee21cb