SQL中的WITH语句（公共表表达式CTE）

WITH语句，也称为公共表表达式（Common Table Expression，CTE），是SQL中一种强大的功能，它允许你创建临时结果集，这些结果集可以在后续的查询中被引用。

基本语法

WITH cte_name AS (SELECT column1, column2, ...FROM table_nameWHERE condition
)
SELECT * FROM cte_name;

主要特点

1. 临时结果集：CTE只在当前查询执行期间存在
2. 可读性：使复杂查询更易于理解和维护
3. 递归能力：支持递归查询（使用WITH RECURSIVE）

使用场景

1. 简化复杂查询

WITH sales_summary AS (SELECT product_id, SUM(quantity) as total_soldFROM salesGROUP BY product_id
)
SELECT p.product_name, s.total_sold
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;

2. 替代子查询

-- 使用子查询
SELECT * FROM (SELECT employee_id, salary FROM employees
) AS emp_data;-- 使用CTE更清晰
WITH emp_data AS (SELECT employee_id, salary FROM employees
)
SELECT * FROM emp_data;

3. 递归查询（WITH RECURSIVE）

WITH RECURSIVE employee_hierarchy AS (-- 基础查询（起点）SELECT employee_id, name, manager_id, 1 as levelFROM employeesWHERE manager_id IS NULLUNION ALL-- 递归部分SELECT e.employee_id, e.name, e.manager_id, eh.level + 1FROM employees eJOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

多个CTE

可以定义多个CTE，用逗号分隔：

WITH 
cte1 AS (SELECT ...),
cte2 AS (SELECT ...),
cte3 AS (SELECT ...)
SELECT ... FROM cte1 JOIN cte2 ON ... JOIN cte3 ON ...;

优点

1. 提高查询可读性和可维护性
2. 避免重复子查询
3. 支持递归查询
4. 可以在同一查询中多次引用

注意事项

1. CTE只在当前查询中有效
2. 不同数据库对CTE的支持可能略有不同
3. 复杂的递归CTE可能导致性能问题

WITH语句是SQL中组织复杂查询的强大工具，特别适用于需要多次引用相同子查询或需要递归处理层次结构数据的场景。