题解 | 商品交易(网易校招笔试真题)
商品交易(网易校招笔试真题)
https://www.nowcoder.com/practice/f257dfc1b55e42e19eec004aa3cb4174
-- 定义公共表达式 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
