没限制数据库吧？ mysql 太渣~用 postgres 了

select * from (
select
g.name,
t.sales_volume_sum,
gg.name,
sum(t.sales_volume_sum) over(partition by g.group_id) total_sum
from (
select
goods_id,
sum(gsr.sales_volume) sales_volume_sum
from goods_sales_record gsr
group by gsr.goods_id
) t
left join goods g
on t.goods_id = g.id

left join goods_group gg
on g.group_id = gg.id

order by t.sales_volume_sum desc
limit 3
) tmp
order by total_sum desc, sales_volume_sum desc