什么是常见的表格表达式？

常见表表达式（CTE）是SQL中的一个有价值的功能，可让您在查询中创建临时结果集。它们简化了复杂的查询，增强代码可读性并提高查询性能。 CTE是使用WITH关键字启动的。

图：CTE语法。来自MariaDB的图像

什么时候使用CTE？

ctes对于：

• 将复杂操作分解为更简单的步骤
• 处理层次数据结构
• 实施大型结果集的分页
• 简化复杂的聚合任务
• 如果您的查询涉及子查询，多个连接或错综复杂的过滤条件，请提高代码的可读性和可维护性

CTES类型

广泛的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通过使用SUM和GROUP BY功能来计算每个部门的总工资。然后，主要查询从CTE获取结果。

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 e
  JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

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

递归CTE的结构如下：

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

是的，递归CTE令人困惑。我本人与他们挣扎了很多。需要很长时间才能了解何时使用它们以及原因。 ð

结论

总而言之，通用表表达式（CTE）为SQL提供了一个强大的工具，可增强复杂查询的可读性，可维护性和效率。

如果您喜欢阅读的内容，请考虑subscribing to my newsletter。
在GitHub，Twitter