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

select name, r_count
from 
(select category_id,count(f.film_id) as r_count 
from film as f inner join film_category as fc
on f.film_id = fc.film_id
where description like '%robot%'
group by category_id) as rfc,
(select category_id, count(film_id) as f_count
from film_category
group by category_id) as ffc,
category as c
where rfc.category_id = ffc.category_id
and ffc.category_id = c.category_id
and f_count>=5;

夺笋啊，

这题最后要求该分类下的电影数要大于等于5， 但是题目没有要求将这个数提取出来， 我提取出来了，所以错了。 审题呢。

这道题的思路也大致讲一下啊。 首先，category与film_category是一体的，一荣俱荣一损俱损，因此，先不用去管category； 其次，在剩余两个表上做文章。

1.1 根据film表，检索出电影描述中存在'robot'的电影编号； 1.2 联结film与film_category，找出电影描述中存在'robot'的电影编号，电影分类； 1.3 联结film与film_category，找出电影描述中存在'robot'的电影分类以及该分类下robot电影数量汇总，记为表1； 2.1 根据film_category创建包括各电影分类以及该分下所有电影数目汇总，记为表2； 3 联立表1，表2与category表，确定过滤条件，就可以了。 源码如下：

-- 1.1
select * from film
where description like '%robot%';
-- 1.3
select category_id,count(f.film_id) as r_count 
from film as f inner join film_category as fc
on f.film_id = fc.film_id
where description like '%robot%'
group by category_id;
-- 2.1
select category_id, count(film_id) as f_count
from film_category
group by category_id;
-- 3
select name, r_count
from 
(select category_id,count(f.film_id) as r_count 
from film as f inner join film_category as fc
on f.film_id = fc.film_id
where description like '%robot%'
group by category_id) as rfc,
(select category_id, count(film_id) as f_count
from film_category
group by category_id) as ffc,
category as c
where rfc.category_id = ffc.category_id
and ffc.category_id = c.category_id
and f_count>=5;