使用通用表表达式 (CTE) 简化复杂的 SQL 查询

什么是通用表表达式？

通用表表达式 (CTE) 是 SQL 中一项非常有用的功能，它允许您在查询中创建临时结果集。CTE 可以简化复杂的查询，增强代码的可读性，并提高查询性能。CTE 可以通过WITH关键字来启动。

图：CTE 语法。图片来自MariaDB

何时使用 CTE？

CTE 尤其适用于：

• 将复杂的操作分解为更简单的步骤
• 处理分层数据结构
• 为大型结果集实现分页
• 简化复杂的聚合任务
• 如果在多个地方需要相同的逻辑，请使用可重用的代码
• 如果您的查询涉及子查询、多个连接或复杂的过滤条件，则提高代码的可读性和可维护性

CTE 的类型

广义上，CTE 可分为：

• 非递归（简单）CTE
• 递归 CTE

1. 简单的通用表表达式

非递归 CTE 简单易懂，不涉及自引用。它们有助于简化复杂的查询、聚合和转换，方法是将它们分解为更小、更易于管理的步骤。

示例：按部门划分的工资总额

WITH department_salary AS (
  SELECT department_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department_id
)
SELECT * FROM department_salary;

这里，CTE使用和函数department_salary计算每个部门的总工资。然后，主查询从 CTE 获取结果。SUMGROUP BY

2. 递归表表达式

递归 CTE 用于处理分层或递归数据结构。它们允许查询引用其自身的输出，从而实现诸如遍历树结构或在图中查找路径之类的操作。

示例：组织层次结构

假设我们有一个名为的表，employees其列为employee_id、name和manager_id，其中manager_id指的是employee_id员工经理的。

WITH RECURSIVE org_hierarchy AS (
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL  -- Root level employees (managers)

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
  FROM employees AS e
  JOIN org_hierarchy AS oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

在此示例中，我们定义了一个名为 的递归 CTE org_hierarchy。初始查询通过选择具有 的员工来检索根级员工（经理）NULL manager_id。CTE 的递归部分使用UNION ALL子句将employees表与 CTE 本身连接起来，使用 连接员工及其各自的经理manager_id。

递归 CTE 的结构如下：

• 锚点查询选择根级别的员工（经理）并为其分配级别 1。
• 递归查询选择向上一次迭代中找到的经理报告的员工，并将级别增加 1。
• 最后的查询检索整个组织层次结构，包括员工及其在层次结构中的各自级别。

是的，递归 CTE 确实让人困惑。我自己也经常遇到这个问题。我花了很长时间才理解什么时候该用它们，以及为什么该用它们。🙃

结论

总之，通用表表达式 (CTE) 对于增强复杂查询的可读性、可维护性和效率非常有效。

在GitHub和Twitter上找到我