题解 | #商品交易(网易校招笔试真题)#

select distinct a.id,a.name,a.weight,a.total from
(select g.id,name,weight,sum(count) over (partition by goods_id) as total
from goods g join trans t
on g.id=t.goods_id) as a
where a.weight<50 and a.total>20
order by a.id
1、易错点：仔细观察一下trans表，发现count列是需要案goods—ID分组聚合的
2、当distinct和order by出现在同一层函数，要确保order by的地段在select的字段中；如果用group by和ORDER BY出现在同一层函数，则select中的所有列要么包含在group by 中要么都是用了聚合函数。