题目
思路
第一次写成这样:
SELECT c.name,COUNT(fc.film_id)
FROM film_category fc LEFT JOIN film f
ON fc.film_id=f.film_id
LEFT JOIN category c
ON fc.category_id=c.category_id
WHERE f.description LIKE '%robot%'
GROUP BY c.name
HAVING COUNT(fc.film_id)>=5;
主要注意 “该分类包含电影总数量(count(film_category.category_id))>=5部”是指全部的电影,并不是指仅包含robot的电影
SELECT c.name,COUNT(fc.film_id)
FROM film_category fc LEFT JOIN film f
ON fc.film_id=f.film_id
LEFT JOIN category c
ON fc.category_id=c.category_id
WHERE f.description LIKE '%robot%'
AND fc.category_id IN (SELECT category_id
FROM film_category
GROUP BY category_id
HAVING COUNT(film_id)>=5 );