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

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

1 两个表拼接，知道产品的销量和name

要知道每个产品的销量 group by product_id;但因为后面需要用到产品类型，所以在第一个with加入category，group by需要把所有非聚合字段都加入

with zhenghe as(select a.product_id,a.name,a.category,b.quantity

from products a join orders b on a.product_id=b.product_id),

jisuan as(select name,sum(quantity)as total_sales,category

from zhenghe

group by product_id,category

order by category,total_sales desc)

2 在category里排序，同一个category 要排成1，2 所以就按category分组排序即可

select name as product_name,total_sales,row_number()over(partition by category )as category_rank

from jisuan