题解 | #查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部#

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5

根据题目要求进行拆分：
1、查找出film.description中包含robot电影对应的分类名称

select
        f.film_id,     # film_id 用于count(film.film_id)
        fc.category_id  
from 
        film f
join
        film_category as fc
on f.film_id = fc.film_id
where 
        description like '%robot%'

2、考虑到需要name字段，根据1的查找结果与category表进行连接，获取计数结果

select
  ca.name,
  count(t.film_id)
from  
      (select
        f.film_id,
        fc.category_id  
      from 
        film f
      join
        film_category as fc
      on f.film_id = fc.film_id
      where 
        description like '%robot%') as t
join category as ca
on ca.category_id = t.category_id
group by ca.name

3、注意后续条件限制，需要在该分类中电影总数量大于等于5，因此需要先找出该分类

select
     c.category_id  
from film_category as fc   
join film as f
on f.film_id = fc.film_id
join category as c
on fc.category_id = c.category_id
group by c.category_id
having count(*) >= 5

4、最后，根据2，结合3使用where进行的条件筛选，即获得最终结果

select
  ca.name,
  count(t.film_id)
from  
      (select
        f.film_id,
        fc.category_id  
      from 
        film f
      join
        film_category as fc
      on f.film_id = fc.film_id
      where 
        description like '%robot%') as t
join category as ca
on ca.category_id = t.category_id
where
  t.category_id in (
       select
         c.category_id  
       from 
         film_category as fc   
       join film as f
       on f.film_id = fc.film_id
       join category as c
       on fc.category_id = c.category_id
       group by c.category_id
       having count(*) >= 5 )
group by ca.name