题解 | #查找在职员工自入职以来的薪水涨幅情况#

select 
	c.name,
	count(1) as num
from
(
select
    film_id
from 
    film 
where 
    description like "%robot%"
)a
inner join 
(
select 
    film_id,
    category_id,
    rank()over(partition by film_id order by last_update desc ) as rn,
    count(*)over(partition by category_id) as rc
from 
    film_category
)b
on a.film_id=b.film_id
inner join 
(
select 
    category_id,
    name,
    rank()over(partition by category_id order by last_update desc ) as rb
from 
    category 
)c
on b.category_id=c.category_id 
where 
b.rn=1 
and c. rb=1
and b.rc>=5
group by 
c.name