with goods_sales as(
-- 获取每种商品的总销售额
select distinct
g.id as goods_id,
g.name as goods_name,
g.group_id,
gg.name as group_name,
sum(gsr.sales_volume) over(partition by
g.id,
g.name, g.group_id,
gg.name) sum_goods_sales_volume,
sum(gsr.sales_volume) over(partition by g.group_id,
gg.name) sum_group_sales_volume
from goods g
inner join goods_group gg on g.group_id=
gg.id inner join goods_sales_record gsr on
g.id= gsr.goods_id
order by sum_goods_sales_volume desc -- 以商品的总销售倒序排列,并取前三名
limit 3
)
select goods_id,
goods_name,
group_id,
group_name,
sum_goods_sales_volume, -- 每种商品的总销售额
sum_group_sales_volume -- 每种分类的总销售额
from goods_sales
order by sum_group_sales_volume desc, sum_goods_sales_volume desc -- 以每种分类的总销售额、商品的总销售倒序排列