子查询与 CTE

一、按返回形态分类

形态返回可用位置
标量子查询单行单列任何表达式位置（SELECT / WHERE / ORDER BY）
列子查询单列多行IN / ANY / ALL 的右侧
行子查询单行多列与行构造器比较 (a, b) = (SELECT ...)
表子查询多行多列FROM 后（派生表）、EXISTS 中
-- 标量：高于全公司平均薪资的员工
SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);

-- 列：属于某几个部门的员工
SELECT * FROM employee WHERE department_id IN (SELECT id FROM department WHERE name LIKE 'R&D%');

-- 行：一次匹配多列
SELECT * FROM employee WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employee GROUP BY department_id LIMIT 1);

-- 表（派生表）：必须起别名
SELECT t.department_id, t.mx FROM (
    SELECT department_id, MAX(salary) AS mx FROM employee GROUP BY department_id
) AS t WHERE t.mx > 10000;

标量子查询返回多行会报错

ERROR 1242: Subquery returns more than 1 row。不确定时加 LIMIT 1，或者改写成 JOIN。

二、相关子查询 vs 非相关子查询

非相关子查询不引用外层的列，只需执行一次，结果可以被缓存：

SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);

相关子查询引用了外层的列，逻辑上对外层每一行都要执行一次：

SELECT * FROM employee e
WHERE salary > (SELECT AVG(salary) FROM employee WHERE department_id = e.department_id);
--                                                                    ^^^^^^^^^^^^^^^^ 引用外层

相关子查询语义清晰，但代价高（近似 O(n×m)）。MySQL 8.0 优化器能把相当一部分相关子查询转换为半连接或派生表，但不要指望它总能转换成功——数据量大时优先改写为 JOIN 或窗口函数：

-- 上面那条的窗口函数改写：一次扫描搞定
SELECT * FROM (
    SELECT e.*, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employee e
) t WHERE salary > dept_avg;

三、IN / EXISTS / ANY / ALL

x = ANY (子查询)     -- 等价于 IN
x <> ALL (子查询)    -- 等价于 NOT IN
x > ALL  (子查询)    -- 比所有值都大，等价于 x > (SELECT MAX(...))
x > ANY  (子查询)    -- 比最小值大，等价于 x > (SELECT MIN(...))

IN 和 EXISTS 该用哪个

流传的"外表大用 IN、内表大用 EXISTS"是 MySQL 5.5 及更早版本的经验。MySQL 8.0 中两者都会被优化器转换为半连接，再从 FirstMatch、LooseScan、Materialization、Duplicate Weedout 等策略中按代价选择，执行计划常常完全一样。

现代的选择标准是语义：

  • 判断"存在性"用 EXISTS / NOT EXISTS——语义直白，且不受 NULL 影响；
  • 值集合明确且很小（常量列表）时用 IN；
  • 永远不要用 NOT IN 配可空的子查询列，见三值逻辑。

EXISTS 内部写什么无所谓（SELECT 1 / SELECT * 性能相同，优化器根本不取列），只判断是否有行返回。

-- NOT EXISTS 为什么不怕 NULL：
-- 它逐行判断"是否存在匹配"，NULL 行匹配不上，自然算作"不存在"，
-- 而 NOT IN 是把整个列表做 AND 比较，一个 UNKNOWN 就毁掉整个表达式。
SELECT * FROM employee e
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = e.id);

四、CTE：WITH 公共表表达式

MySQL 8.0 起支持。CTE 的价值在于可读性和复用：

WITH dept_stat AS (
    SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS cnt
    FROM employee
    GROUP BY department_id
),
big_dept AS (
    SELECT * FROM dept_stat WHERE cnt >= 10     -- CTE 之间可以互相引用
)
SELECT d.name, b.avg_salary
FROM big_dept b
JOIN department d ON d.id = b.department_id
ORDER BY b.avg_salary DESC;

CTE 相比派生表的三个优势：

  1. 可以被多次引用，派生表每次出现都要重写一遍；
  2. 自上而下阅读，嵌套三层以上的派生表几乎不可读；
  3. 支持递归。

CTE 不一定更快

MySQL 的 CTE 既可能被合并（merge）进外层查询，也可能被物化（materialize）成临时表。被多次引用、含聚合/DISTINCT/LIMIT/UNION 时通常物化。物化的临时表没有索引，如果外层要对它做大量查找，可能反而更慢——此时可考虑用 NO_MERGE / MERGE 优化器提示，或干脆落成真实临时表并建索引。

另外 MySQL 的 CTE 不是"执行屏障"（PostgreSQL 12 以前的 WITH 是），谓词可能被下推进去，这一点对性能是好事。

五、递归 CTE ⭐⭐⭐

处理树形 / 图结构、生成序列的利器。结构固定为三部分：

WITH RECURSIVE cte_name (列名列表) AS (
    SELECT ...              -- ① 种子查询（非递归部分），提供起点
    UNION ALL               -- ② UNION ALL 保留全部；UNION 会去重，可用于防环
    SELECT ...              -- ③ 递归部分，必须引用 cte_name 自身
    FROM cte_name JOIN ...
    WHERE 终止条件
)
SELECT * FROM cte_name;

例 1：生成连续日期序列（补全统计报表中"没有数据的那一天"）

WITH RECURSIVE dates (d) AS (
    SELECT DATE('2024-01-01')
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2024-01-31'
)
SELECT dates.d, COALESCE(SUM(o.amount), 0) AS amount
FROM dates
LEFT JOIN orders o ON DATE(o.created_at) = dates.d
GROUP BY dates.d;

例 2：查询某个经理下面的所有下属（任意层级）

WITH RECURSIVE sub (id, name, lvl) AS (
    SELECT id, name, 0 FROM employee WHERE id = 1        -- 起点：1 号经理
    UNION ALL
    SELECT e.id, e.name, s.lvl + 1
    FROM employee e
    JOIN sub s ON e.manager_id = s.id
)
SELECT * FROM sub WHERE lvl > 0;

例 3：拆分逗号分隔的字符串（处理反范式数据）

WITH RECURSIVE split (id, part, rest) AS (
    SELECT id,
           SUBSTRING_INDEX(tags, ',', 1),
           CONCAT(SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ',', 1)) + 2), '')
    FROM article
    UNION ALL
    SELECT id,
           SUBSTRING_INDEX(rest, ',', 1),
           SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
    FROM split WHERE rest <> ''
)
SELECT id, part FROM split;

递归的三个限制

  1. 递归部分只能引用 CTE 自身一次，且不能出现在 LEFT JOIN 的右侧；
  2. 递归部分不能包含聚合函数、窗口函数、GROUP BY、ORDER BY、DISTINCT；
  3. 默认最大递归深度由 cte_max_recursion_depth 控制（默认 1000），超出报错 ERROR 3636。数据中存在环（A 的上级是 B，B 的上级是 A）时会一直递归下去，务必在 WHERE 里加深度限制或用路径字段判环。

六、派生表、视图、临时表的选择

生命周期是否可建索引适用
派生表 / CTE单条语句❌（8.0 可能自动加派生表键）中间结果，一次性
视图 VIEW持久❌（本身无数据）封装复杂查询、权限隔离
临时表 TEMPORARY TABLE会话级✅中间结果被反复关联、需要索引加速

视图有 MERGE 和 TEMPTABLE 两种算法：含聚合、DISTINCT、UNION、LIMIT、窗口函数的视图只能物化，无法把外层谓词下推，成为性能黑洞。视图适合封装语义，不适合当性能优化手段。

实战

第二高的薪水 ⭐⭐

👉 Leetcode 链接-176在新窗口打开

Employee(id, salary)。查询第二高的薪水，不存在时返回 null。

考点

这题的难点全在边界：只有一条记录 / 全部薪水相同时，必须返回 NULL 而不是空集。

select
    (select distinct salary
     from Employee
     order by salary desc
     limit 1 offset 1) as SecondHighestSalary

把查询包成标量子查询是关键：标量子查询无结果时求值为 NULL，天然满足要求。直接写 select distinct salary ... limit 1,1 在无结果时返回的是空表，不符合题意。

另一种写法用 MAX 的 NULL 特性：

select max(salary) as SecondHighestSalary
from Employee
where salary < (select max(salary) from Employee)

推广到第 N 高（LeetCode 177在新窗口打开）时要注意，MySQL 的 LIMIT 后不能直接跟表达式，需要先算好变量：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET N
  );
END

连续出现的数字 ⭐⭐

👉 Leetcode 链接-180在新窗口打开

Logs(id, num)，id 连续自增。查找所有至少连续出现三次的数字。

考点

"连续行"问题的两种范式：自连接错位、或窗口函数 LAG/LEAD。

select distinct
    l1.num as ConsecutiveNums
from
    Logs l1, Logs l2, Logs l3
where
    l1.id = l2.id - 1
    and l2.id = l3.id - 1
    and l1.num = l2.num
    and l2.num = l3.num

这个解法有前提

它假设 id 严格连续无空洞。真实数据中删除过行就会漏判，稳健写法应基于窗口函数按顺序取相邻行：

select distinct num as ConsecutiveNums from (
    select num,
           lag(num, 1) over (order by id) as p1,
           lag(num, 2) over (order by id) as p2
    from Logs
) t where num = p1 and num = p2

体育馆的人流量 ⭐⭐⭐

👉 Leetcode 链接-601在新窗口打开

Stadium(id, visit_date, people)，id 自增连续。查找 people >= 100 且连续三行及以上的记录，按 visit_date 排序。

考点

连续区间（gaps and islands）。经典技巧：id - ROW_NUMBER() 在连续段内是常量。

with filtered as (
    select id, visit_date, people,
           id - row_number() over (order by id) as grp
    from Stadium
    where people >= 100
)
select id, visit_date, people
from filtered
where grp in (select grp from filtered group by grp having count(*) >= 3)
order by visit_date

只用自连接的写法（列举三行的三种相对位置）也能过，但可读性和扩展性都差很多——把"连续 3 天"改成"连续 7 天"时，上面的解法只需要改一个数字。

每个产品在不同商店的价格（列转行）

👉 Leetcode 链接-1795在新窗口打开

Products(product_id, store1, store2, store3)，转成 (product_id, store, price) 的长表，价格为 null 的不输出。

select product_id, 'store1' as store, store1 as price from Products where store1 is not null
union all
select product_id, 'store2', store2 from Products where store2 is not null
union all
select product_id, 'store3', store3 from Products where store3 is not null

下一步 👉 窗口函数

上次更新:
贡献者: Joe