Pandaaaa906 最近的时间轴更新 Pandaaaa906 最近的时间轴更新 |
Pandaaaa906V2EX 第 602327 号会员，加入于 2022-11-19 00:44:03 +08:00 |
Pandaaaa906 最近回复了
2022-11-19 10:34:30 +08:00 回复了 qiyong 创建的主题 › 程序员 › 面试中遇到的一道 sql 题 |
没限制数据库吧？ 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
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
Select Language