Sakila数据库中查询参演电影数超平均值演员的SQL报错求助

问题背景 #

我使用Sakila数据库的film表（含film_id字段）和film_actor表（含actor_id、film_id字段），想要找出参演电影数量超过所有演员平均值的演员。

尝试的SQL语句 #

select actor_id, count(film_actor.film_id) as film_count
from film, film_actor
where film.film_id = film_actor.film_id
group by actor_id
having film_count > avg(film_count)

报错信息 #

Error Code: 1111. Invalid use of group function

去掉HAVING子句后能正常得到每个演员的参演数量，示例结果如下：

...

报错原因 #

你不能在HAVING子句里直接用avg(film_count)。因为film_count是按actor_id分组后算出的单个演员的电影数，avg(film_count)需要的是所有演员电影数的整体平均值，但当前GROUP BY actor_id的层级里，avg()只能计算当前分组内的聚合值，无法跨分组计算整体平均值，这属于聚合函数的非法嵌套使用场景。

正确实现方案 #

要完成需求，得先算出所有演员参演电影数的平均值，再筛选出超过这个值的演员，这里提供两种可行写法：

方法1：子查询嵌套 #

select actor_id, count(fa.film_id) as film_count
from film_actor fa
group by actor_id
having count(fa.film_id) > (
    -- 先统计每个演员的电影数，再求这些数值的平均值
    select avg(actor_film_count)
    from (
        select count(film_id) as actor_film_count
        from film_actor
        group by actor_id
    ) as actor_counts
);

方法2：CTE+子查询（MySQL 8.0及以上版本支持） #

with actor_film_counts as (
    -- 先统计每个演员的参演电影数
    select actor_id, count(film_id) as film_count
    from film_actor
    group by actor_id
)
-- 筛选出数量超过平均值的演员
select actor_id, film_count
from actor_film_counts
where film_count > (select avg(film_count) from actor_film_counts);

补充提示 #

原SQL里关联film表是多余的——film_actor表已经包含有效的film_id（通常是关联film表的外键），直接统计每个actor_id对应的film_id数量就能得到参演电影数，不需要额外关联film表。

内容的提问来源于stack exchange，提问作者Yair