select
t.product_name,
t.total_sales,
row_number() over(partition by t.category order by t.total_sales desc,t.product_id asc) as category_rank
from
(select
p.name as product_name,
o.product_id as product_id,
p.category as category,
sum(o.quantity) as total_sales
from orders o left join products p
on o.product_id=p.product_id
group by p.name,o.product_id,p.category
order by category asc,total_sales desc) t
where t.category is not null;-------排除category为null,非常重要!