题解 | #查找描述信息中包含robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部#
查找描述信息中包含robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
http://www.nowcoder.com/practice/3a303a39cc40489b99a7e1867e6507c5
链接 + 子查询 + sum(if())函数
select
c.name,
count(*)
from film f
left join film_category fc
on f.film_id = fc.film_id
left join category c on fc.category_id = c.category_id
where description like '%robot%'
and
(
select
sum(if(category_id = c.category_id,1,0)) sum_c
from film_category
having sum_c >=5
)
c.name,
count(*)
from film f
left join film_category fc
on f.film_id = fc.film_id
left join category c on fc.category_id = c.category_id
where description like '%robot%'
and
(
select
sum(if(category_id = c.category_id,1,0)) sum_c
from film_category
having sum_c >=5
)