子查询、CTE 与集合操作

中间答案与粒度

先统计每用户收入，再按渠道求用户收入均值，需要两个聚合层次。一次 GROUP BY 不能同时保留这两个粒度，应先形成每用户一行，再连接 users 并按渠道聚合。放在 FROM 的子查询叫派生表，需要别名；WITH 定义公共表表达式，让每层输入和输出容易阅读。名字最好体现粒度，例如 user_net。

CTE 名称只在当前语句有效，不是永久表，也不承诺物化存储。优化器可能合并或物化它，因此用了 CTE 不代表一定更快。它的价值首先是明确步骤。完成一层时先检查键唯一和金额守恒，再继续连接，能够更快定位重复累计来自哪个阶段。

三种子查询

标量子查询至多返回一行一列，例如全表 AVG(amount)，可以作为比较值。返回多行会报错，不能随意 LIMIT 1 掩盖不确定性。IN 接受一列集合，EXISTS 只关注有无匹配，它们表达不同的业务问题。相关子查询引用外层键，通过 o.user_id=u.user_id 把订单范围绑定到当前用户。

查询没有支付订单的用户时，NOT EXISTS 正好表达不存在满足条件的记录。内层 SELECT 1 的数值不会参与收入计算，只用于存在性判断。理解时可按每用户枚举订单，实际优化器可能转换为半连接，不能把逻辑推导当成数据库必然采用的物理执行过程。

纵向合并的去重边界

UNION ALL 纵向叠加两组记录并保留重复，UNION 对整个输出行去重，可能产生额外排序或哈希工作。两边列数相同，位置对应的类型应兼容，最终名称来自第一段查询。合并不是按键配对，不能用 UNION 代替 JOIN。整个合并完成后再 ORDER BY 才定义结果顺序。

只选择 user_id 时，一月和二月同一用户会被 UNION 合成一行；加入月份后两行月份不同就不会合并。去重粒度由输出列决定，并不是原表主键自动生效。不要把 UNION 当作处理一切重复的万能方法，先调查重复源和筛选范围是否重叠。

分母决定查询起点

示例从 users 出发左连每用户收入，无订单收入显示零，因此分母是全部注册用户。若直接从已支付订单的汇总出发，平均值分母变成支付过的用户，指标已经改变。两者都能执行，只有业务定义能判断哪一个正确；把分子分母写在报告里可以避免误解。

不必把每个表达式都套一层 CTE，阶段划分应对应筛选、粒度变换或复杂逻辑复用。一条 WITH 中多个 CTE 用逗号隔开，后面的可以引用前面的；下一条语句需重新定义。MySQL 还支持递归 CTE，但本章先掌握非递归拆解，复杂生成序列作为后续扩展。

从结果到验证

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

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

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

完整示例与实际输出

WITH user_net AS (
  SELECT user_id,SUM(amount-refund_amount) net_amount FROM orders
  WHERE status IN ('paid','refunded') GROUP BY user_id
), registered_net AS (
  SELECT u.user_id,u.channel,COALESCE(n.net_amount,0) net_amount
  FROM users u LEFT JOIN user_net n ON n.user_id=u.user_id
) SELECT channel,COUNT(*) registered_users,AVG(net_amount) mean_net
  FROM registered_net GROUP BY channel ORDER BY channel;
SELECT user_id FROM users WHERE country='CN'
UNION ALL SELECT user_id FROM users WHERE channel='organic' ORDER BY user_id;
SELECT user_id FROM users WHERE country='CN'
UNION SELECT user_id FROM users WHERE channel='organic' ORDER BY user_id;

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

channel	registered_users	mean_net
ads	10	91.200000
organic	10	15.625000
referral	10	456.000000

user_id
3
3
6
6
9
9
12
12
15
15
18
18
21
21
24
24
27
27
30
30

user_id
3
6
9
12
15
18
21
24
27
30

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

两项动手任务

官方资料与掌握状态

• MySQL WITH
• MySQL UNION

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