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

WITH product_sales AS (
  -- 第一步：计算每个产品的销量和分类（和简化版内层一致）
  SELECT 
    p.name AS product_name,
    p.category,
    SUM(IFNULL(o.quantity, 0)) AS total_sales
  FROM products p 
  JOIN orders o ON p.product_id = o.product_id
  GROUP BY p.name, p.category
)
-- 第二步：排名+筛选字段+排序
SELECT 
  product_name,
  total_sales,
  RANK() OVER(PARTITION BY category ORDER BY total_sales DESC) AS category_rank
FROM product_sales
ORDER BY category, total_sales DESC;

JOIN (内连接，推测的“答案”用法)

• 含义：JOIN（或 INNER JOIN）只保留在两个表中都能找到匹配记录的数据。