个人认为本题纯语法层面的考察,为了结果而显示结果,无实际应用意义

整理自网络,分享细化的解题思路:
-- 第一步:将film表与film_category进行连接,找出描述信息(film.description)中包含robot的的film_id以及fc.category_id;
-- 第二步:将film_category与category进行连接,找出category_id、name及每种电影类型包含电影总数量(count(film_category.category_id))>=5部;
-- 第三步:将第一步和第二步的联结表以 category_id 连接,查询出电影分类名称name,并以name或者category_id分组统计电影数目(count(film.film_id))即可得出结果;


select b.name as  '分类名称category.name',
       count(a.film_id) as '电影数目count(film.film_id)'
from
    (select f.film_id,
            fc.category_id
     from film as f inner join film_category as fc
     on f.film_id=fc.film_id
     where f.description regexp 'robot') as a
inner join 
     (select c.category_id,
             c.name,
             count(fc.category_id) as num
      from category as c inner join film_category as fc
      on c.category_id=fc.category_id
      group by c.category_id
      having count(fc.category_id)>=5) as b
on a.category_id=b.category_id
group by b.category_id;