SQL关键字三分钟入门：WITH —— 公用表表达式让复杂查询更清晰

在实际的数据库开发和分析中，我们常常会遇到复杂的多层嵌套查询，这样的 SQL 语句不仅难以阅读，也容易出错。

这时候就需要使用一个非常实用又优雅的关键字 —— WITH！

它可以帮助我们将复杂的子查询提取出来并命名，从而提升代码可读性、复用性和维护性。这个功能也被称为 CTE（Common Table Expressions，公用表表达式）。

 1.什么是 WITH？

WITH 是 SQL 中用于定义临时结果集的关键字。这些临时结果集可以在后续查询中像普通表一样被引用，并且只在当前查询执行期间存在。

你可以把它理解为：“先写好一个中间结果，后面可以直接拿来用”。

 2.基本语法

WITH cte_name AS (
    -- 子查询内容
    SELECT ...
)
-- 后续主查询中使用 cte_name
SELECT * FROM cte_name;

• cte_name 是你给中间结果集起的名字。
• 可以定义多个 CTE，用逗号分隔。

 3.示例讲解

假设我们有一个 orders 表，记录了订单信息：

 示例1：计算每个客户的订单总金额（简单 CTE 使用）

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_amount > 500;

结果：

👉 这里我们先定义了一个 CTE customer_totals 来计算每位客户的总消费金额，然后主查询筛选出金额大于500的客户。

 示例2：多个 CTE 的使用（分步处理复杂逻辑）

WITH
-- 第一步：统计每位客户的总消费
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
),
-- 第二步：根据总消费划分客户等级
customer_levels AS (
    SELECT customer_id, total_amount,
    CASE
        WHEN total_amount > 1000 THEN '高级客户'
        WHEN total_amount BETWEEN 500 AND 1000 THEN '中级客户'
        ELSE '普通客户'
    END AS level
    FROM customer_totals
)
-- 最终查询：展示客户等级信息
SELECT * FROM customer_levels;

结果：

 通过多个 CTE 分步骤处理，整个查询逻辑更加清晰易懂。

 示例3：递归 CTE（以员工层级为例）

递归 CTE 是 WITH 的一种高级用法，常用于处理树形结构或层级数据（如组织架构、分类目录等）。

假设我们有一个 employees 表：

WITH RECURSIVE employee_hierarchy AS (
    -- 初始查询：没有上级的员工（即 CEO）
    SELECT employee_id, name, manager_id, CAST(name AS TEXT) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归部分：查找下属员工
    SELECT e.employee_id, e.name, e.manager_id,
           CAST(eh.hierarchy_path || ' → ' || e.name AS TEXT)
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

结果：

 这个例子展示了如何用递归 CTE 构建一个组织层级路径，非常适合处理树状结构数据。

4. 总结对比表