法一:(窗口函数)
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