我可以使用派生表的别名作为比较子查询的表引用吗？

我使用 MySQL 已经有一段时间了，但仍在学习使用它的最佳方法。

为了练习，我编写了一个查询，以获取 sakila 示例数据库中每个演员的最有特色的电影类别，我们可以使用 MySQL 工作台获取该数据库。结果如下所示：

我通过以下查询获得结果。但是，我不得不重复同一个查询两次，一次在from外部查询部分，第二次在where子查询部分。

select  `MCPA`.`first_name` ,`MCPA`.`last_name`,
        group_concat(`MCPA`.`category`) as `most_featured_categories`
    from  
    (
        SELECT  AC.first_name as `first_name` ,AC.last_name as `last_name` ,
                CA.`name` as `category` ,
                count( distinct FI.film_id ) as `number_of_movies` ,
                count( distinct FI.film_id ) /
                     fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
                    as `percentage_of_movies_done`
            from  actor as AC
            inner join  film_actor as FIAC  ON AC.actor_id = FIAC.actor_id
            inner join  film as FI  ON FIAC.film_id = FI.film_id
            inner join  film_category as FICA  ON FI.film_id = FICA.film_id
            inner join  category as CA  ON FICA.category_id = CA.category_id
            group by  AC.first_name, AC.last_name, CA.`name` 
    ) as `MCPA`
    where  `MCPA`.`number_of_movies` = 
    (
        SELECT  max(`SubMCPA`.`number_of_movies`)
            from  
            (
                SELECT  AC.first_name as `first_name`, AC.last_name as `last_name`,
                        CA.`name` as `category` ,
                        count( distinct FI.film_id ) as `number_of_movies` ,
                        count( distinct FI.film_id ) /
                             fNumber_Of_Films_Per_Actor (AC.first_name,
                                AC.last_name
                             ) * 100
                           as `percentage_of_movies_done`
                    from  actor as AC
                    inner join  film_actor as FIAC  ON AC.actor_id = FIAC.actor_id
                    inner join  film as FI  ON FIAC.film_id = FI.film_id
                    inner join  film_category as FICA  ON FI.film_id = FICA.film_id
                    inner join  category as CA
                          ON FICA.category_id = CA.category_id
                    group by  AC.first_name, AC.last_name, CA.`name` 
            ) as `SubMCPA`
            where  1=1
              and  `MCPA`.`first_name` = `SubMCPA`.`first_name`
              and  `MCPA`.`last_name` = `SubMCPA`.`last_name` 
    )
    group by  `MCPA`.`first_name`, `MCPA`.`last_name` ;

我曾尝试使用

    select max(`SubMCPA`.`number_of_movies`)
    from `MCPA` as `SubMCPA`
    where 1=1
        and `MCPA`.`first_name` = `SubMCPA`.`first_name`
        and `MCPA`.`last_name` = `SubMCPA`.`last_name`

但它没有用；好像workbench不识别派生表，需要我重新定义它。

我有其他方法可以避免输入代码吗

    (
        select AC.first_name as `first_name`
            ,AC.last_name    as `last_name`
            ,CA.`name` as `category`
            ,count( distinct FI.film_id ) as `number_of_movies`
            ,count( distinct FI.film_id ) /
                    fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
                  as `percentage_of_movies_done`
        from actor as AC
        inner join film_actor as FIAC
        on AC.actor_id = FIAC.actor_id
        inner join film as FI
        on FIAC.film_id = FI.film_id
        inner join film_category as FICA
        on FI.film_id = FICA.film_id
        inner join category as CA
        on FICA.category_id = CA.category_id
        group by AC.first_name, AC.last_name, CA.`name`
     ) as

两次？

** 我试图通过先创建一个表然后引用它来避免它，但是也许还有其他方法吗？

此外，似乎在一个查询中完成所有操作会提高性能。

任何建议将不胜感激，希望我的问题很清楚，我没有描述所有的数据库结构以避免太乏味的问题。