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

法一：（窗口函数）

SELECT DISTINCT id,name,weight,total FROM 
(SELECT goods.id,name,weight,SUM(count) over (partition by trans.goods_id) total
 FROM goods JOIN trans ON goods.id=trans.goods_id) t1
WHERE weight<50 AND total>20
ORDER BY id

法二：给goods加一个total列即可。（这个方法就可以了，感觉我老开窗开习惯了，捂脸）

SELECT id,name,weight,total FROM goods 
JOIN
(SELECT goods_id, SUM(count) total FROM trans GROUP BY goods_id) t1
ON goods.id=t1.goods_id
WHERE weight<50 AND total>20
ORDER BY id