CTE – 公用表表达式- WITH AS

在 SQL 中，WITH AS 语法通常用于创建 公用表表达式（CTE, Common Table Expression）。它可以让你的查询结构更清晰、更容易阅读，尤其适合处理复杂的多层嵌套查询。

一、基本语法格式

WITH 临时表名 AS (
    子查询语句
)
SELECT ... FROM 临时表名;

说明：

• WITH 后面定义一个或多个临时表（即 CTE）。
• 每个临时表必须有 名称，并用 AS 指定它的查询内容。
• 定义完后，就可以在主查询中像普通表一样使用这个临时表。

二、单个 CTE 示例

假设我们有个表

-- 假设 sales 表结构：
CREATE TABLE sales (salesperson_id INT, sales_amount DECIMAL);

WITH sales_summary AS (
    SELECT 
        salesperson_id,
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT salesperson_id, total_sales
FROM sales_summary
WHERE total_sales > 100000;

解释： 先在 WITH 中统计每个销售员的业绩，然后在主查询里筛选出业绩超过 10万 的销售员。

三、多个 CTE 示例

你可以同时定义多个临时表。

WITH
    sales_summary AS (
        SELECT salesperson_id, SUM(sales_amount) AS total_sales
        FROM sales
        GROUP BY salesperson_id
    ),
    top_sales AS (
        SELECT salesperson_id, total_sales
        FROM sales_summary
        WHERE total_sales > 100000
    )
SELECT *
FROM top_sales;

四、递归 CTE 示例（高级用法）

递归 CTE 用于层级结构查询（比如组织架构、目录树等）。

sql

WITH RECURSIVE subordinates AS (
    SELECT employee_id, manager_id, employee_name
        FROM employees
    WHERE manager_id IS NULL  -- 找出顶层经理
    UNION ALL
        SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

解释： 这个递归查询会顺着经理层级一直往下找所有下属。

注意 RECURSIVE 它是数据库的关键词，用于指定 CTE 的类型（递归型）。

五、小结