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

-- 定义公共表达式 CTE
WITH T AS(
    SELECT
    goods.id,
    goods.name,
    goods.weight,
    SUM(trans.count) AS total
    -- 计算每种商品的总购买个数
FROM
    trans
    -- 左连接 goods 表
    LEFT JOIN goods ON goods.id = trans.goods_id
-- 筛选条件：质量小于50
WHERE
    goods.weight < 50
-- 按照商品id进行分组
GROUP BY
    trans.goods_id
)
SELECT
    id,
    name,
    weight,
    total
FROM
    T
-- 筛选条件：购买个数大于等于20
WHERE
    total >= 20    
-- 按照商品id升序排列
ORDER BY
    id ASC