CTE – 公用表表达式- WITH AS

收心

发布于 2025-12-24 17:42:42

2480

在 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 的类型（递归型）。

五、小结

项目	作用
WITH ... AS (...)	定义临时的结果集
可定义多个临时表	用逗号分隔多个 CTE
可递归	用 WITH RECURSIVE 实现层级查询
优点	代码更清晰、易维护、可代替多层嵌套子查询

特殊说明： 上述文章均是作者实际操作后产出。烦请各位，请勿直接盗用！转载记得标注原文链接：www.zanglikun.com 第三方平台不会及时更新本文最新内容。如果发现本文资料不全，可访问本人的Java博客搜索：标题关键字。以获取最新全部资料 ❤ 免责声明： 本站文章旨在总结学习互联网技术过程中的经验与见解。任何人不得将其用于违法或违规活动！所有违规内容均由个人自行承担，与作者无关。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-11-11，如有侵权请联系 cloudcommunity@tencent.com 删除

语法