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

思路：

1. 先对trans表中，根据goods_id分组，使用having过滤出购买数量总和大于20的数据。
2. 连接goods表与上面的临时表，即可查询出所需数据

select g.*, t.total
from goods as g join (select goods_id, sum(count) as total
											from trans
											group by goods_id
											having total > 20) as t
on g.id = t.goods_id
where g.weight < 50
order by g.id

也可使用 with ... as ... 将步骤一作为一个临时表，思路是一样的

with t as (select goods_id, sum(count) as total
					 from trans
					 group by goods_id
					 having total > 20)
select g.*, t.total
from goods as g 
join t
on g.id = t.goods_id
where g.weight < 50
order by g.id