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

select g.id,	g.name,g.weight,sum(t.count) as total

from 
goods g
join 
trans t 
on g.id = t.goods_id

group by g.id
having sum(t.count)>20 and weight<50

order by g.id

select

t.goods_id as id,

g.name,

g.weight,

sum(count) as total

from

goods g

join

trans t on g.id = t.goods_id

group by

t.goods_id, g.name, g.weight

having

sum(count) > 20and g.weight < 50

order by

t.goods_id asc;

这里和标准答案对比一下，我在group by 后面没有对name和weight进行分组，查了一下，出于严谨考虑，还是应该加上

也就是group by 之后

SELECT子句中出现的字段必须满足以下条件之一：

1. 出现在GROUP BY子句中（作为分组依据）；
2. 被聚合函数（如SUM/COUNT/MAX等）包裹。