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

WITH T AS
(
    SELECT 
        G.id
        ,G.name
        ,G.weight
        ,SUM(T.count) count
    FROM (
        SELECT id,name,SUM(weight) weight FROM goods GROUP BY id,name
        ) G
    LEFT JOIN trans T ON G.id = T.goods_id
    GROUP BY 
        G.id
        ,G.name
) 
SELECT
    T.*
FROM T
WHERE T.weight < 50 
    AND T.count > 20
GROUP BY T.id
;