连接与数据粒度

计算机基础 · SQL

导出进度导入备份

前置知识：聚合、HAVING 与条件统计

本章目录

• 本章学习目标
• 连接是配对过程，不是把两张表贴在一起
• 逐步计算左连接与筛选
• 订单总额与明细连接的重复累计
• NULL、连接检查与 pandas 对照
• 从结果到验证
• 交互演示：观察机制
• 完整示例与实际输出
• 五道自测：先回答，再核对
• 两项动手任务
• 官方资料与掌握状态

本章学习目标

• 按主键、外键和业务粒度选择连接
• 解释一对多与多对多造成的行数变化
• 识别外连接过滤和连接后重复累计

前置知识：聚合、HAVING 与条件统计

连接是配对过程，不是把两张表贴在一起

users 一行表示一个注册用户，orders 一行表示一笔订单，order_items 一行表示订单中的一个商品明细。user_id 在 users 中唯一，在 orders 中可以重复或为空。连接条件 users.user_id = orders.user_id 表示把每个用户与所有匹配的订单配对。若用户一有两笔订单，连接结果中他的用户信息会出现两次；这不是数据库出错，而是结果粒度已经变成用户与订单的配对。

设左表键为甲、乙、丙，右表键为甲、甲、乙。内连接得到三行，左连接得到四行，因为丙仍保留一行，右侧列补成 NULL。如果右表再添加一个甲，甲对应行数再加一。若左表也有两个甲，甲会形成两乘三等于六个配对。连接前检查键的重复程度，连接后检查结果粒度，是分析人员最重要的防错习惯之一。

逐步计算左连接与筛选

查询所有用户及已支付订单时，把订单状态限制写进 ON。LEFT JOIN 先为每个用户查找满足连接条件的订单；没有满足条件的订单仍保留用户。paid 与 refunded 都属于曾支付订单，净额为 amount 减 refund_amount。零订单用户的净额应显示零，但订单侧其他列的空值要保留，帮助区分没有匹配与真实字段未知。

如果把 o.status IN ('paid','refunded') 放进 WHERE，补空行也要接受这一筛选，未知比较不能为真，于是无匹配用户被删除。要统计零订单用户，请用 COUNT(o.order_id)，不能用 COUNT(*)；补空的那一行会被星号计数。对于未知用户的订单，左表从 users 出发会排除它们，所以还需单独核对 orders 中的孤儿键与空键，避免误把注册用户收入解释成全部订单收入。

订单总额与明细连接的重复累计

一笔金额一百元的订单有三个商品明细。订单连明细后有三行，每行订单金额都为一百，SUM(o.amount) 会得到三百。正确做法取决于目标：计算订单总额就直接从订单聚合；计算商品销售额就用 qty 乘 unit_price；同时展示订单指标与商品指标时，先分别聚合到相同的订单或用户粒度，再连接。

SUM(DISTINCT amount) 不是通用修复。如果有两笔不同订单恰好都为一百元，DISTINCT 会把它们当成同一数值，只累计一次。DISTINCT 处理的是列值，不理解订单身份。示例先按 order_id 聚合明细，形成每订单一行的数量，再与订单表连接。用户级汇总同理，应先把订单聚合成每用户一行，最后与用户表连接。

NULL、连接检查与 pandas 对照

SQL 的 NULL = NULL 不是真，因此两条空连接键不会因为相等连接条件而配对。pandas.merge 默认会让两侧空键互相匹配，所以同一任务移植到 pandas 时，要先处理或排除空键并检查 validate='many_to_one' 等约束。不能用某个不存在的数字随意替换 NULL，否则可能把无关联的记录连在一起。

连接检查至少包括左表主键唯一性、右表每键数量、连接后每键行数、未匹配记录和汇总前后金额。数据库外键可以减少部分孤儿记录，但课程 orders 特意不设用户外键，让你练习识别异常。反连接用 NOT EXISTS 或 LEFT JOIN 后检查非空主键是否为 NULL；检查主键优于检查 nullable 业务列，因为一条真实匹配记录也可能有空业务字段。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

交互演示：观察机制

完整示例与实际输出

下载 sql-join.sql ↓复制代码

SELECT u.user_id, COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount-o.refund_amount),0) AS net_amount
FROM users u LEFT JOIN orders o
  ON o.user_id=u.user_id AND o.status IN ('paid','refunded')
GROUP BY u.user_id ORDER BY u.user_id;
WITH item_totals AS (
  SELECT order_id,SUM(qty) AS item_qty FROM order_items GROUP BY order_id
)
SELECT o.order_id,o.amount,COALESCE(i.item_qty,0) AS item_qty
FROM orders o LEFT JOIN item_totals i ON i.order_id=o.order_id
ORDER BY o.order_id;
SELECT o.order_id,o.user_id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.user_id=o.user_id)
ORDER BY o.order_id;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

user_id	paid_orders	net_amount
1	3	144.00
2	3	720.00
3	2	25.00
4	3	144.00
5	3	720.00
6	2	25.00
7	3	120.00
8	3	720.00
9	2	18.75
10	3	144.00
11	3	600.00
12	1	12.50
13	2	96.00
14	2	480.00
15	2	25.00
16	2	96.00
17	2	480.00
18	1	12.50
19	2	96.00
20	2	360.00
21	1	12.50
22	2	72.00
23	2	480.00
24	2	25.00
25	0	0.00
26	0	0.00
27	0	0.00
28	0	0.00
29	0	0.00
30	0	0.00

order_id	amount	item_qty
1	48.00	2
2	240.00	3
3	12.50	1
4	48.00	2
5	240.00	3
6	12.50	1
7	48.00	2
8	240.00	3
9	12.50	1
10	48.00	2
11	240.00	3
12	12.50	1
13	48.00	2
14	240.00	3
15	12.50	1
16	48.00	2
17	240.00	3
18	12.50	1
19	48.00	2
20	240.00	3
21	12.50	1
22	48.00	2
23	240.00	3
24	12.50	1
25	48.00	2
26	240.00	3
27	12.50	1
28	48.00	2
29	240.00	3
30	12.50	1
31	48.00	2
32	240.00	3
33	12.50	1
34	48.00	2
35	240.00	3
36	12.50	1
37	48.00	2
38	240.00	3
39	12.50	1
40	48.00	2
41	240.00	3
42	12.50	1
43	48.00	2
44	240.00	3
45	12.50	1
46	48.00	2
47	240.00	3
48	12.50	1
49	48.00	2
50	240.00	3
51	12.50	1
52	48.00	2
53	240.00	3
54	12.50	1
55	48.00	2
56	240.00	3
57	12.50	1
58	48.00	2
59	240.00	3
60	12.50	1

order_id	user_id
60	NULL

五道自测：先回答，再核对

01 · 左表两个甲、右表三个甲，等值连接几行？

先写自己的答案

查看提示

对每个左行枚举右行。

查看参考答案

六行，连接生成二乘三对；两侧都不唯一时不能假定每用户一行。

02 · 为什么 LEFT JOIN 后 COUNT(*) 可能将零订单记为一？

先写自己的答案

查看提示

无匹配时是否仍有输出行？

查看参考答案

左连接会生成一条补空行；COUNT(*) 数行，COUNT(o.order_id) 忽略补空的主键。

03 · 为何状态条件放 WHERE 会删除零订单用户？

先写自己的答案

查看提示

把补空行代入比较。

查看参考答案

NULL IN (...) 为未知，不通过 WHERE；将订单侧条件放 ON 才保留左表所有用户。

04 · SUM(DISTINCT amount) 能解决明细重复吗？

先写自己的答案

查看提示

考虑不同订单金额相同。

查看参考答案

不能。不同订单金额相同也会去重。应在订单粒度聚合，或先聚合明细再连接。

05 · SQL 与 pandas 空键连接有何区别？

先写自己的答案

查看提示

未知不等于未知。

查看参考答案

SQL 普通等值比较不匹配 NULL，pandas.merge 默认匹配空键；对照计算需预先对齐空键规则。

两项动手任务

任务 1 · 列出没有任何订单的用户，按用户编号排序。

提示

反连接检查订单主键。

完整参考解答与检查方式

SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id=u.user_id WHERE o.order_id IS NULL ORDER BY u.user_id; 这检查所有订单，与没有已支付订单不同。

任务 2 · 保留所有用户，给出订单笔数和商品数量，防止明细放大笔数。

提示

先把明细压缩到订单粒度。

完整参考解答与检查方式

WITH t AS (SELECT order_id,SUM(qty) q FROM order_items GROUP BY order_id) SELECT u.user_id,COUNT(o.order_id) order_count,COALESCE(SUM(t.q),0) item_count FROM users u LEFT JOIN orders o ON o.user_id=u.user_id LEFT JOIN t ON t.order_id=o.order_id GROUP BY u.user_id ORDER BY u.user_id; t 每订单一行，因此订单计数不被放大。

官方资料与掌握状态

• MySQL JOIN 语法

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。

A · 独立完成B · 提示后会C · 需重学待学

记录尽量保存在当前浏览器，建议定期导出备份。