SQL中with关键字详解

WITH 关键字的作用

SQL 中的 WITH 关键字用于创建临时结果集，称为公用表表达式（CTE）。CTE 仅在当前查询中有效，类似于临时视图，可以简化复杂查询的结构，提高可读性和可维护性。

基本语法

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

使用场景

递归查询
WITH 支持递归 CTE，常用于处理层次结构数据（如组织结构、树形数据）。

WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询（锚成员）
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询（递归成员）
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

多 CTE 组合
可以在一个查询中定义多个 CTE，用逗号分隔。

WITH 
department_stats AS (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
),
high_paid_depts AS (
    SELECT department_id
    FROM department_stats
    WHERE avg_salary > 10000
)
SELECT * FROM high_paid_depts;

性能优化

CTE 可以替代子查询，但需注意：

• 递归 CTE 可能产生性能问题，需合理设置终止条件
• 某些数据库（如 PostgreSQL）会物化 CTE 结果，而其他数据库（如 SQL Server）可能仅作为查询重写

与其他语法的比较

与临时表对比

• CTE 不需要显式创建/删除，生命周期限于当前语句
• 临时表可跨多个查询使用，但需要额外管理

与视图对比

• CTE 是临时的，视图是持久化的
• 视图可重复使用，CTE 仅限当前查询