WITH 和 WITH RECURSIVE SQL通用表表达式（Common Table Expression，CTE）

WITH 和 WITH RECURSIVE 的概念

WITH

WITH 语句用于定义普通的通用表表达式（Common Table Expression，CTE），它可以帮助简化复杂的查询结构，使查询更具可读性和可维护性。WITH 语句生成一个临时结果集，该结果集在整个查询过程中是可见的。

WITH RECURSIVE

WITH RECURSIVE 是 WITH 语句的递归版本，它允许 CTE 引用自身来实现递归逻辑。递归 CTE 非常适用于需要递归操作的场景，比如处理层次结构数据或生成序列。

WITH 的使用方法与注意事项

使用方法

WITH 语句用于定义一个普通的通用表表达式（CTE），帮助简化复杂的查询。其基本语法如下：

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

示例

假设我们有一个员工表 employees，需要查询每个部门的平均薪资：

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name, ds.avg_salary
FROM employees e
JOIN DepartmentSalaries ds ON e.department_id = ds.department_id;

注意事项

  1. 仅在当前查询中可用：CTE 只在定义它的查询中可用，一旦查询结束，CTE 就会被销毁。
  2. 性能考虑：CTE 适用于简化查询逻辑，但在处理非常大的数据集时，可能会有性能问题，因为 CTE 结果集是在内存中处理的。
  3. 嵌套和多次引用：可以在一个查询中定义多个 CTE，也可以在同一个查询中多次引用同一个 CTE。

高级用法

嵌套 CTE

可以嵌套多个 CTE，使查询更加简洁和可读：

WITH 
DeptSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
HighEarners AS (
    SELECT employee_id, name, department_id, salary
    FROM employees
    WHERE salary > 100000
)
SELECT he.employee_id, he.name, ds.avg_salary
FROM HighEarners he
JOIN DeptSalaries ds ON he.department_id = ds.department_id;

WITH RECURSIVE 的使用方法与注意事项

使用方法

WITH RECURSIVE 语句用于定义递归 CTE，允许 CTE 引用自身以实现递归逻辑。其基本语法如下：

WITH RECURSIVE cte_name AS (
    -- 初始查询部分
    SELECT ...
    UNION ALL
    -- 递归查询部分
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

示例

假设我们需要生成一个日期范围：

WITH RECURSIVE DateRange AS (
    SELECT '2024-08-01' AS date_value
    UNION ALL
    SELECT DATE_ADD(date_value, INTERVAL 1 DAY)
    FROM DateRange
    WHERE date_value < '2024-08-31'
)
SELECT date_value FROM DateRange;

注意事项

  1. 递归条件：递归 CTE 必须有一个递归终止条件，否则会导致无限循环。
  2. 递归层数限制：不同的数据库系统对递归的层数有不同的限制，需注意数据库系统的配置。
  3. 性能考虑：递归 CTE 在处理层次结构数据时非常有用，但在处理大量数据或深度递归时，可能会导致性能问题。

高级用法

复杂递归查询

递归 CTE 可以处理复杂的递归逻辑，如路径查找、树状结构数据等：

WITH RECURSIVE OrgChart AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;

WITH 和 WITH RECURSIVE 与临时表的区别

临时表的概念

临时表（Temporary Tables）是在物理存储中创建的临时存储结构，通常用于存储临时数据。

区别

  1. 存储位置：临时表是在物理存储中创建，而 CTE 是在内存中生成的临时结果集。
  2. 生命周期：临时表的生命周期从创建开始，直到会话结束或显式删除为止。CTE 只在当前查询的执行过程中存在，一旦查询结束，CTE 就会被销毁。
  3. 可重用性：临时表可以在同一会话中的多个查询中重复使用，而 CTE 只能在定义它的查询中使用。
  4. 性能：CTE 在处理小数据集时效率较高，但对于大数据集，临时表可能表现更好，因为临时表可以利用索引和缓存。

临时表示例

创建和使用临时表的示例：

-- 创建临时表
CREATE TEMPORARY TABLE TempEmployees (
    employee_id INT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO TempEmployees (employee_id, name, salary)
VALUES (1, 'Alice', 50000), (2, 'Bob', 60000);

-- 查询临时表
SELECT * FROM TempEmployees;

-- 删除临时表
DROP TEMPORARY TABLE TempEmployees;

CTE 与临时表的结合使用

可以先使用 CTE 生成数据，然后插入到临时表中：

-- 创建临时表
CREATE TEMPORARY TABLE DateTable (
    date_value DATE
);

-- 使用递归CTE生成日期范围并插入临时表
WITH RECURSIVE DateRange AS (
    SELECT '2024-08-01' AS date_value
    UNION ALL
    SELECT DATE_ADD(date_value, INTERVAL 1 DAY)
    FROM DateRange
    WHERE date_value < '2024-08-31'
)
INSERT INTO DateTable (date_value)
SELECT date_value FROM DateRange;

-- 查询临时表
SELECT * FROM DateTable;

总结

WITH 和 WITH RECURSIVE 是 SQL 中非常强大的工具，可以帮助简化查询、处理递归逻辑和生成临时结果集。它们与临时表的区别在于存储位置、生命周期和性能。通过掌握这些工具，可以在实际项目中更高效地处理各种查询场景。