SQL中CTE是什么？

在 SQL 中，CTE（Common Table Expression）即公共表表达式，是一种临时命名的结果集，你可以在 SQL 语句（如 SELECT、INSERT、UPDATE 或 DELETE）中引用它。CTE 有助于简化复杂查询，增强代码可读性，还能在同一查询里多次复用。下面为你详细介绍 CTE 的用法：

基础语法

WITH cte_name (column1, column2, ...) AS (
    -- CTE的查询定义
    SELECT expression1, expression2, ...
    FROM table_name
    WHERE condition
)
-- 主查询，引用CTE
SELECT * FROM cte_name;

应用场景及示例

1. 简化嵌套子查询

假设要查询 "orders" 表中高于平均金额的订单，使用 CTE 的写法如下：

WITH average_orders AS (
    SELECT AVG(amount) AS avg_amount
    FROM orders
)
SELECT order_id, amount
FROM orders, average_orders
WHERE orders.amount > average_orders.avg_amount;

2. 多步数据处理

若需分步骤筛选和计算数据，可使用多个 CTE，示例如下：

WITH 
filtered_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
order_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM filtered_orders
    GROUP BY customer_id
)
SELECT customers.name, order_totals.total
FROM customers
JOIN order_totals ON customers.id = order_totals.customer_id;

3. 递归 CTE（处理层级数据）

递归 CTE 可用于处理如组织结构、产品分类等层级关系，示例如下：

WITH RECURSIVE employee_hierarchy AS (
    -- 初始查询（锚成员）
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询（递归成员）
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

关键要点

1. 作用域：CTE 仅在定义它的查询中有效，查询执行完毕后就会被销毁。
2. 递归限制：递归 CTE 必须包含终止条件，防止出现无限循环。
3. 性能方面：虽然 CTE 能提升代码可读性，但在某些情况下，执行效率可能不如临时表或子查询。
4. 兼容性：所有主流数据库（如 MySQL 8.0+、PostgreSQL、SQL Server、Oracle 等）都支持 CTE。

CTE 是 SQL 中一个强大的工具，在处理复杂查询时，能让代码更具可读性和可维护性。