Pandaaaa906's recent timeline updates Pandaaaa906's recent timeline updates |
Pandaaaa906V2EX member #602327, joined on 2022-11-19 00:44:03 +08:00 |
Pandaaaa906's recent replies
Nov 19, 2022 Replied to a topic by 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