题解 | 每个商品的销售总额

关键：group by和窗口函数partition by的组合用法。一开始考虑到select中不可出现group by未提取的字段，故使用了子查询和表连接（解法1），但其实可以通过group by多个字段来解决这个问题，当字段之间为包含或对应关系时，相当于只对最细分的字段进行了group by，而select中可以出现这几个字段（解法2）。解法2简化了逻辑。

select name product_name,total_sales,rank()over(partition by category order by total_sales desc,t1.product_id) category_rank
from(
select o.product_id,sum(quantity) total_sales
from orders o join products p on o.product_id=p.product_id
group by o.product_id
) t1 join products p on t1.product_id = p.product_id
order by category,total_sales desc

select name product_name,sum(quantity) total_sales,rank()over(partition by category order by sum(quantity) desc,a.product_id) category_rank
from products a
join orders b on a.product_id = b.product_id
group by product_name,a.product_id,category
order by category, total_sales desc