题解 | #商品交易(网易校招笔试真题)#
商品交易(网易校招笔试真题)
http://www.nowcoder.com/practice/f257dfc1b55e42e19eec004aa3cb4174
第一个时使用聚合函数sum
SELECT g.id,g.name,g.weight,t.total
FROM goods g,(
select goods_id,sum(count) total
from trans
group by goods_id
) t
WHERE g.id=t.goods_id
and t.total>20
and g.weight<50;
第二个使用窗口函数sum
with t as(
select goods_id,
sum(count) over (partition by goods_id) total
from trans
)
SELECT distinct g.id,g.name,g.weight,t.total
FROM goods g,t
WHERE g.id=t.goods_id
and t.total>20
and g.weight<50;
