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

WITH t1 AS (
    SELECT a.goods_id,
           b.name,
           SUM(a.count) AS total,
           MAX(b.weight) AS weight_
    FROM trans a
    LEFT JOIN goods b
    ON a.goods_id = b.id
    GROUP BY a.goods_id, b.name
    HAVING total > 20 AND weight_ <50
)
SELECT goods_id AS id,
       name,
       weight_ AS weight,
       total
FROM t1
ORDER BY id ASC;

# 这道题关键在于重量是不可以叠加的，但数量可以！