题解 | 分析每个商品在不同时间段的销售情况

#查询每个商品的销售情况，包含的字段：商品 ID、商品名称、该商品在 2024 年第二季度的销售总额，该商品在所属类别的销售排名，所属供应商。查询出来的数据按照商品 ID 升序排列。
select a.product_id,product_name,ifnull(q2_2024_sales_total,0) as q2_2024_sales_total,rank()over(partition by category order by q2_2024_sales_total desc) as category_rank,supplier_name
from product_info as a
left join(
    select product_id,sum(total_amount) as q2_2024_sales_total
    from order_info
    where order_date between '2024-04-01' and '2024-06-30'
    group by product_id
) as b
on a.product_id = b.product_id
left join supplier_info as c
on a.product_id = c.product_id
order by a.product_id

注意inner join会过滤掉product E