Common Table Expressions, 简称 CTE, 是 SQL 中一个强大的工具, 它能让查询更清晰易懂, 还能处理一些看似棘手的问题, 比如递归查询. 让我们一起揭开 CTE 的神秘面纱吧!

CTE 的基本结构

CTE 的语法结构非常清晰, 以 WITH 关键字开始, 后跟 CTE 的名称(可以自定义名称), 然后在括号内定义一个 SQL 查询语句, 最后在主查询中引用这个 CTE. 就像这样:

WITH cte_名称 AS (
  -- CTE 查询语句 (通常是 SELECT 语句)
)
SELECT *
FROM cte_名称
-- ... 其他主查询部分 ...

括号里的查询语句可以是各种 SELECT 语句, 例如, 最简单的:

WITH simple_cte AS (
  SELECT 1
)
SELECT *
FROM simple_cte;

猜猜这段代码会输出什么?

1

没错, 结果就是 1.

CTE 的第一个妙用: 查询重构, 提升可读性

个人非常喜欢把 CTE 视为 SQL 查询的"重构工具".

当 SQL 查询变得复杂难懂时, CTE 可以分解查询逻辑, 让代码更易读, 更易维护.

举个例子, 假设有一个 employees (员工) 表, 想找出所有薪水低于平均工资的员工. 用普通的子查询, 我们可以这样写:

SELECT *
FROM employees AS a
JOIN (
  SELECT AVG(salary) AS average_salary
  FROM employees
) AS b ON a.salary < b.average_salary;

这个查询语句ok, 但读起来略显吃力, 特别是中间那个子查询, 让整个语句的结构变得有些混乱. 用 CTE 来重写这个查询, 会怎样呢?

WITH average_salary_cte AS (
  SELECT AVG(salary) AS average_salary
  FROM employees
)
SELECT *
FROM employees AS a
JOIN average_salary_cte AS b ON a.salary < b.average_salary;

用 WITH 关键字定义了一个名为 average_salary_cte 的 CTE, 这个 CTE 的作用就是计算平均工资. 然后在主查询中, 像使用普通表一样 JOIN 这个 CTE. 运行结果和之前的子查询完全一致, 觉得哪个版本更易读呢?

-- 使用 CTE 的版本
WITH average_salary_cte AS (
  SELECT AVG(salary) AS average_salary
  FROM employees
)
SELECT *
FROM employees AS a
JOIN average_salary_cte AS b ON a.salary < b.average_salary;

-- 使用子查询的版本
SELECT *
FROM employees AS a
JOIN (
  SELECT AVG(salary) AS average_salary
  FROM employees
) AS b ON a.salary < b.average_salary;

CTE 版本更清晰, 因为它把计算平均工资的逻辑独立出来, 主查询的结构也更简洁. 虽然这个例子比较简单, 但可以想象, 当查询逻辑更复杂时, CTE 的优势会更加明显.

它可以将一个庞大的查询分解成多个小的, 易于理解的逻辑单元, 就像代码重构一样, 让 SQL 代码更优雅.

CTE 的第二个绝技: 递归查询, 处理层级数据

除了提升可读性, CTE 最令人兴奋的功能是递归. 递归 CTE 允许在 CTE 内部引用自身, 从而实现递归查询. 这在处理层级结构数据时非常有用, 例如组织架构, 树形结构等.

什么是递归 CTE? 简单来说, 递归 CTE 包含两个关键部分, 通过 UNION ALL 操作符连接:

1. 锚点成员 (Anchor Member): 这是一个非递归的查询, 它定义了递归的起始结果集, 并且只执行一次.
2. 递归成员 (Recursive Member): 这是一个递归的查询, 它引用了 CTE 自身. 递归成员会重复执行, 每次迭代都基于上一次迭代的结果集进行计算, 直到满足终止条件.

例子: 生成数字序列

为了更好地理解递归 CTE, 先来看一个简单的例子: 如何使用递归 CTE 生成一个包含 1 到 10 的数字序列?

WITH RECURSIVE counter_cte AS (
  SELECT 1 AS num  -- 锚点成员: 起始值为 1
  UNION ALL
  SELECT num + 1   -- 递归成员: 每次加 1
  FROM counter_cte
  WHERE num < 10   -- 终止条件: 当 num >= 10 时停止递归
)
SELECT *
FROM counter_cte;

逐步解析这段代码:

• WITH RECURSIVE counter_cte AS (...): 声明一个名为 counter_cte 的递归 CTE. RECURSIVE 关键字是必须的, 它告诉 SQL 引擎这是一个递归 CTE.
• SELECT 1 AS num: 这是锚点成员, 它返回一个包含数字 1 的结果集, 并将其命名为 num 列. 这是递归的起点.
• UNION ALL: 连接锚点成员和递归成员的结果集.
• SELECT num + 1 FROM counter_cte WHERE num < 10: 这是递归成员. 它从 counter_cte 中选择 num 列, 并将 num 值加 1. 关键在于 FROM counter_cte, 这里 CTE 引用了自身, 实现了递归. WHERE num < 10 是终止条件, 当 num 达到 10 时, 这个 WHERE 子句将不再返回任何结果, 递归就此结束.
• SELECT * FROM counter_cte: 主查询, 用于从 CTE 中检索结果.

如果我们运行这段代码, 就会得到一个包含数字 1 到 10 的结果集:

num
----
1
2
3
4
5
6
7
8
9
10

进阶应用: 查询员工层级关系

现在, 看一个更实用的例子, 使用递归 CTE 查询员工的层级关系. employees中包含 manager_id 列, 表示员工的直属经理 ID. 组织结构如下:

Arthur (CEO)
  |-- William (VP)
  |    |-- Randy
  |    |-- ...
  |-- Mary (VP)
  |    |-- ...
  |-- Bob (VP)
  |    |-- ...

我们想查询出员工 "Randy" 以及他所有层级的经理, 一直追溯到 CEO "Arthur". 使用递归 CTE 可以轻松实现:

WITH RECURSIVE org_cte AS (
  SELECT *          -- 锚点成员: 选择员工 "Randy" 的信息
  FROM employees
  WHERE name = 'Randy'
  UNION ALL
  SELECT b.*        -- 递归成员: 选择经理的信息
  FROM org_cte AS a  -- 递归引用 CTE 自身
  JOIN employees AS b ON a.manager_id = b.id -- 连接条件: 员工的 manager_id 等于经理的 id
)
SELECT *
FROM org_cte;

这段代码的逻辑是:

1. 锚点成员: 先找到员工 "Randy" 的记录, 作为递归的起始点.
2. 递归成员: 在每次递归迭代中, 从 org_cte (上次迭代的结果集) 中获取员工的 manager_id, 然后 JOIN employees 表, 找到对应的经理信息. 这样就实现了层层向上追溯经理的功能.
3. 终止条件: 递归的终止条件是当追溯到 CEO "Arthur" 时, CEO 的 manager_id 为 NULL, 在 employees 表中找不到对应的记录, 因此 JOIN 操作不会返回任何结果, 递归就停止了.

运行这段代码, 将得到包含 "Randy", "William" (Randy 的经理) 和 "Arthur" (William 的经理, 即 CEO) 的结果集, 完美地展现了 Randy 的管理层级关系.

跨数据库的 CTE

虽然在示例中使用了 SQLite 数据库, 但 CTE 是 SQL 标准功能, 在主流关系型数据库(如 PostgreSQL, MySQL, SQL Server, Oracle 等)都得到了支持. 不过, 不同数据库在 CTE 的具体语法上可能存在细微差异, 使用时最好查阅对应数据库的文档.

总结

CTE 是 SQL 中一个非常实用的功能, 它能:

• 提升查询可读性: 通过分解复杂查询, 让 SQL 代码更清晰, 易于理解和维护.
• 实现递归查询: 轻松处理层级结构数据, 例如组织架构, 树形结构等.

无论你是 SQL 新手还是资深专家, 掌握 CTE 都能让你的 SQL 技能更上一层楼, 编写出更高效, 更优雅的查询语句.