这简单啊,不过销量字段类型先改成 int 吧,
求组内总销量用 sum(sales_volume) over(partition by group_id) ,
销量排序 rank()over(order by sales_volume desc)
# mysql8
select t.goods_name `商品名`,
t.sales_volume `商品销量`,
t.group_name `商品组名`,
t.group_sales_volume `商品组总销量`
from (
select t.goods_id,
t.sales_volume,
g.name goods_name,
gp.name group_name,
sum(t.sales_volume)over(partition by g.group_id) group_sales_volume,
rank() over (order by t.sales_volume) sales_rank
from goods_sales_record t
left join goods g on
g.id = t.goods_id
left join goods_group gp on
gp.id = g.group_id
) t
where t.sales_rank<=3
order by t.group_sales_volume desc,t.sales_volume desc;