真的是头疼,找不到工作。我哭了。。。
(先计算每个电影类型对应的电影数量,然后再关联查询)
select r2.name,count(1) from (select fc.category_id from film f left join film_category fc on f.film_id=fc.film_id where f.description like '%robot%')r1 left join (select fc.category_id,c.name,COUNT(1) as num 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 group by fc.category_id,c.name)r2 on r1.category_id = r2.category_id where r2.num>=5