题解 | #牛客的课程订单分析(三)#

方法一：连接了3张表，其中一张是虚拟表，做出来了。下面第2种方法使用select后的子查询更容易理解，但是最后一个字段无法隐藏，实际工作中可以用，逻辑是对的，想要解题只能是方法一。

方式1：

SELECT count_all.name,count(*)
from (
    SELECT c.category_id,c.name,count(*)
    from film f
    inner join film_category fc
    on fc.film_id=f.film_id
    inner join category c
    on c.category_id=fc.category_id
    group by c.name
    HAVING count(*)>=5
) count_all
inner join film_category fc
on fc.category_id=count_all.category_id
inner join film f 
on fc.film_id=f.film_id
where f.description like '%robot%';

方式2：

SELECT c.name,count(*),(
            SELECT count(*)
            from film f
            inner join film_category fc
            on fc.film_id=f.film_id
            inner join category c
            on c.category_id=fc.category_id
            group by c.name
            HAVING count(*)>=5
) count_a
from film f
inner join film_category fc
on fc.film_id=f.film_id
inner join category c
on c.category_id=fc.category_id
where f.description like '%robot%'
group by c.name
HAVING count_a>=5;