SQL291. 商品交易（网易校招笔试真题）

题目描述

查找"购买个数超过20，质量小于50的商品，按照商品id升序排序"。

输出格式：商品id、商品名、质量、购买总数（total）

表结构

表1：goods（商品表）

CREATE TABLE goods (
    id      INT(11)     NOT NULL PRIMARY KEY COMMENT '商品id',
    name    VARCHAR(10)                     COMMENT '商品名',
    weight  INT(11)     NOT NULL             COMMENT '商品质量'
);

字段名	类型	说明
id	INT(11)	商品id，主键
name	VARCHAR(10)	商品名
weight	INT(11)	商品质量

表2：trans（交易表）

CREATE TABLE trans (
    id       INT(11) NOT NULL PRIMARY KEY COMMENT '交易id',
    goods_id INT(11) NOT NULL              COMMENT '商品id',
    count    INT(11) NOT NULL              COMMENT '商品购买个数'
);

字段名	类型	说明
id	INT(11)	交易id，主键
goods_id	INT(11)	商品id
count	INT(11)	商品购买个数

示例数据

goods

id	name	weight
1	A1	100
2	A2	20
3	B3	29
4	T1	60
5	G2	33
6	C0	55

trans

id	goods_id	count
1	3	10
2	1	44
3	6	9
4	1	2
5	2	65
6	5	23
7	3	20
8	2	16
9	4	5
10	1	3

预期输出

id	name	weight	total
2	A2	20	81
3	B3	29	30
5	G2	33	23

解释：

• 商品 2（A2）：交易 5 买了 65 个 + 交易 8 买了 16 个 = 81 个，质量 20 < 50 ✓
• 商品 3（B3）：交易 1 买了 10 个 + 交易 7 买了 20 个 = 30 个，质量 29 < 50 ✓
• 商品 5（G2）：交易 6 买了 23 个 = 23 个，质量 33 < 50 ✓

商品 1（A1）总购买数 = 49，但质量 100 > 50 ✗ 商品 4（T1）质量 60 > 50 ✗ 商品 6（C0）总购买数 = 9 < 20 ✗

---

解题思路

第一步：理解需求

题目有两个关键条件：

1. 购买个数超过20：指同一商品的累计购买总数 > 20（不是单笔交易）
2. 质量小于50：指 goods.weight < 50

输出需要包含商品信息和购买总数。

第二步：分析表结构

• goods 表存储商品的基本信息（id、name、weight）
• trans 表存储每笔交易的购买数量（一个商品可能有多笔交易）

需要将两表关联，然后按商品分组汇总。

第三步：确定 SQL 方案

1. JOIN：通过 goods.id = trans.goods_id 关联两表
2. GROUP BY：按商品分组（GROUP BY g.id, g.name, g.weight）
3. SUM()：计算每组的购买总数 SUM(t.count)
4. HAVING：分组后过滤，SUM(t.count) > 20 AND g.weight < 50
5. ORDER BY：按商品id升序

第四步：编写并验证 SQL

注意 weight < 50 这个条件是针对每个商品本身的属性，可以放在 WHERE 或 HAVING 中。放在 WHERE 中可以提前过滤，提高效率。

---

完整 SQL 实现

-- 核心解法：JOIN + GROUP BY + HAVING
SELECT
    g.id,
    g.name,
    g.weight,
    SUM(t.count) AS total
FROM goods AS g
JOIN trans AS t
    ON g.id = t.goods_id
GROUP BY g.id, g.name, g.weight
HAVING SUM(t.count) > 20 AND g.weight < 50
ORDER BY g.id ASC;

优化版本（将 weight 条件提前到 WHERE）：

SELECT
    g.id,
    g.name,
    g.weight,
    SUM(t.count) AS total
FROM goods AS g
JOIN trans AS t ON g.id = t.goods_id
WHERE g.weight < 50
GROUP BY g.id, g.name, g.weight
HAVING SUM(t.count) > 20
ORDER BY g.id ASC;

---

解法详解

第一步：JOIN 关联两表

SELECT g.id, g.name, g.weight, t.count
FROM goods AS g
JOIN trans AS t ON g.id = t.goods_id;

关联后的结果（部分）：

g.id	g.name	g.weight	t.count
1	A1	100	44
1	A1	100	2
1	A1	100	3
2	A2	20	65
2	A2	20	16
3	B3	29	10
3	B3	29	20

第二步：GROUP BY 分组 + SUM 聚合

SELECT g.id, g.name, g.weight, SUM(t.count) AS total
FROM goods AS g
JOIN trans AS t ON g.id = t.goods_id
GROUP BY g.id, g.name, g.weight;

分组聚合后：

g.id	g.name	g.weight	total
1	A1	100	49
2	A2	20	81
3	B3	29	30
4	T1	60	5
5	G2	33	23
6	C0	55	9

第三步：HAVING 过滤

HAVING SUM(t.count) > 20 AND g.weight < 50

过滤掉：

• 商品 1：weight = 100 ≮ 50 ✗
• 商品 4：weight = 60 ≮ 50，total = 5 ≯ 20 ✗
• 商品 6：total = 9 ≯ 20 ✗

保留：商品 2、3、5

---

知识点总结

1. GROUP BY 分组聚合

SELECT 列1, 列2, 聚合函数(列3)
FROM 表
GROUP BY 列1, 列2;

GROUP BY 将数据按指定列分组，每组应用聚合函数。常见聚合函数：

• SUM()：求和
• COUNT()：计数
• AVG()：平均值
• MAX() / MIN()：最大/最小值

2. WHERE vs HAVING

子句	执行时机	过滤对象
WHERE	分组前	原始数据行
HAVING	分组后	聚合后的分组结果

规则：能用 WHERE 过滤的尽量用 WHERE，可以减少分组的数据量，提升性能。

3. GROUP BY 的列要完整

MySQL 中 SELECT 的非聚合列必须出现在 GROUP BY 中（严格模式下）。推荐写法：

GROUP BY g.id, g.name, g.weight  -- 包含所有 SELECT 中的非聚合列

---

易错点总结

1. 误解"购买个数超过20"

-- 错误理解：单笔交易的 count > 20
WHERE t.count > 20  -- 这样只会筛选单笔大订单，不是累计总数

正确理解是累计购买总数 > 20，需要使用 SUM(t.count) 并在 HAVING 中过滤。

2. WHERE 中不能直接用聚合函数

-- 错误！WHERE 中不能用聚合函数
WHERE SUM(t.count) > 20  -- 语法错误

聚合条件必须用 HAVING。

3. 忘记 weight 条件

有些同学只写了 HAVING SUM(t.count) > 20，遗漏了 g.weight < 50 的条件。

4. ORDER BY 顺序

题目要求按商品id升序，默认是升序（ASC），也可以显式写出：

ORDER BY g.id ASC   -- 升序（从小到大）
ORDER BY g.id DESC  -- 降序（从大到小）

---

扩展思考

• 如果要求"单笔交易购买个数超过20"？ 用 WHERE t.count > 20，不需要 GROUP BY。
• 如果要求"平均每次购买个数超过20"？ 用 HAVING AVG(t.count) > 20。
• 如果 trans 表中存在 goods_id 在 goods 表中不存在的情况？ 使用 LEFT JOIN 可以暴露这种数据不一致问题。
• 性能优化：在 trans.goods_id 上建立索引，JOIN 和 GROUP BY 都会更快。

---

相关题目

• SQL292. 网易云音乐推荐(网易校招笔试真题)
• SQL230. 汇总各个部门当前员工的title类型的分配数目