MySQL 子查询与 CTE

当一个查询需要另一个查询的结果时，可选择子查询或 CTE。先看结果形状：需要一个值、若干行，还是只判断存在。示例共用 SELECT 方法速查中的样例数据，CTE 使用 MySQL 8.0+ 语法，以 8.4 手册为准。

按需求选择

需求	写法	注意事项
拿一个统计值参与比较	WHERE amount > (SELECT AVG(amount) FROM orders)	标量子查询至多一行一列；空结果为 NULL
匹配一个值集合	WHERE id IN (SELECT customer_id FROM orders)	多列匹配需要对应的行构造，不与标量查询混用
至少存在一条记录	WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id)	关注是否有行，不关注 SELECT 1 的值
没有任何匹配	WHERE NOT EXISTS (...)	明确相关条件；避免 NOT IN 中的 NULL 陷阱
先汇总再查	FROM (SELECT ... GROUP BY ...) AS totals	派生表必须有别名
给中间查询起名、分步骤表达	WITH totals AS (...) SELECT ... FROM totals	CTE 仅在一条语句内有效，不自动缓存或提升性能

高于整体平均金额

SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY id;

样例总额 1500、三笔订单，平均值为 500，预期仅返回 101 / 800。使用 > 不会包含恰好等于平均值的 102。

查没有完成订单的用户

SELECT u.id, u.name
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = u.id AND o.status = 'completed'
)
ORDER BY u.id;

预期为 3 / Wu。这与“没有任何订单”不同：只有待支付订单的用户也会满足本查询。新增边界数据时，应专门验证这种差别。

先汇总，再保留全部用户

SELECT u.id, u.name, COALESCE(t.total, 0) AS total
FROM users AS u
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
) AS t ON t.customer_id = u.id
ORDER BY u.id;

预期 Lin=1300、Chen=200、Wu=0。这里每个中间结果最多一行对应一个用户，便于继续关联其他汇总，避免两个明细表连接后重复累加。

用 WITH 分步骤统计

WITH completed_totals AS (
  SELECT customer_id, SUM(amount) AS paid
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT u.id, u.name, t.paid
FROM completed_totals AS t
JOIN users AS u ON u.id = t.customer_id
WHERE t.paid >= 500
ORDER BY t.paid DESC, u.id;

预期仅 Lin=800。CTE 将“统计完成订单”和“筛选高消费客户”写成两个可读步骤。优化器可能合并或物化中间结果，不能只凭 WITH 或子查询层数判断快慢。

递归 CTE：生成连续日期

WITH RECURSIVE days(d) AS (
  SELECT DATE('2024-01-01')
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2024-01-03'
)
SELECT d FROM days ORDER BY d;

预期为 1 月 1、2、3 日。递归部分必须有终止条件，并受递归深度限制；可以用日期序列左连接按日汇总结果，为零订单日期补 0。它不是无限循环或默认适合大型日期范围的方案。

上述结果按样例推导，未在本次连接 MySQL 实跑。查询性能请结合实际表、索引与 EXPLAIN 检查。

参考：WITH、EXISTS 与 NOT EXISTS、派生表。