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

SELECT 
t4.name product_name,
t3.total_sales,
t3.category_rank
FROM (
    SELECT 
    t1.product_id,
    SUM(t2.quantity) total_sales,
    DENSE_RANK() OVER(partition by t1.category order by COUNT(t1.product_id)*SUM(t2.quantity) DESC) category_rank
    FROM products t1,orders t2
    WHERE t1.product_id = t2.product_id
    GROUP BY t1.product_id
) t3
LEFT JOIN products t4
ON t3.product_id = t4.product_id
ORDER BY t4.category,t3.total_sales DESC