SQL | 多表连接 I 查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量＞=5部

题目

https://www.nowcoder.com/practice/3a303a39cc40489b99a7e1867e6507c5?tpId=82&&tqId=29780&rp=1&ru=/activity/oj&qru=/ta/sql/question-ranking

思路

第一次写成这样：

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 );