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

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

1.首先对trans表用商品id 进行分组求和 形成新表a，里面字段是商品id和total

！注意：一定要起别名，清楚要查询的数据，确保运行能查出来

(select t.goods_id,sum(t.count) as total from trans t group by t.goods_id ) as a

2.然后表商品goods g和新表a内连接 ，都含商品id

select g.id,g.name,g.weight,a.total from goods as g

inner join (select t.goods_id,sum(t.count) as total from trans t group by t.goods_id ) as a

on g.id=a.goods_id

3.最后限定条件，

where a.total>20 and g.weight<50

order by id