查找robot的宇宙最简写法
查找描述信息中包含robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
http://www.nowcoder.com/questionTerminal/3a303a39cc40489b99a7e1867e6507c5
将题意分解成如下步骤:
查找关于robot的电影
( select * from film where description='robot' ) l1
从file_category中筛选出大于五部的电影
( select category_id, from film_category group by category_id having count(*)>=5 )fc
注意:下面通过where in 从这个fc中筛选出>=5个的category_id
查询为robot的category_id,且电影多余5的的分组中满足robot条件的总个数,起别名为为表 l2
select fc.category_id,count(*) cnt from ( select * from film_category where category_id in ( select category_id from film_category group by category_id having count(*)>=5 ) ) fc inner join ( select * from film where description like '%robot%' ) l1 on fc.film_id=l1.film_id group by fc.category_id;
### 将l2与category表进行内连接,查询category.name 与l2.count(film_id),就这么简短愉快的得到结果啦!!! select c.name,l2.cnt from category c inner join l2 on l2.category_id=c.category_id;