CTE查询的概念

CTE（Common Table Expression，通用表表达式）是在SQL中用WITH子句定义的命名临时结果集，它的作用域仅限当前 SQL 语句执行期间，适合简化复杂查询、避免重复子查询，还支持递归场景。

应用场景：

1、如果需要再一个SQL里面多次引用同一个子查询，使用CTE只需要定义一次（典型）

2、类似视图一样，但是不想把SQL语句保存到数据库中（随差随丢）。

3、递归实现

基本语法：

特点：

• 临时性：不存储在磁盘，仅在查询执行时动态生成，执行结束后立即释放。
• 可读性：通过命名将复杂子查询模块化，让逻辑更直观
• 递归能力：支持递归查询（需RECURSIVE关键字），是处理树形结构（如组织架构）、序列生成的唯一简洁方式。
• 作用域：仅在定义它的 SQL 语句中有效，无法被其他独立查询调用（区别于临时表）。
• 复用性：同一查询中可多次引用同一 CTE，避免重复编写子查询

使用场景和语法

单CTE

WITH 结果集名称 (列名1, 列名2, ...)  -- 列名可选，默认继承子查询列名
AS (
    -- 子查询：生成临时结果集的逻辑（支持SELECT/JOIN/WHERE等任意查询语法）
    SELECT 列1, 列2 
    FROM 表名 
    WHERE 过滤条件
)
-- 主查询：像使用普通表一样引用CTE
SELECT * FROM 结果集名称;

• WITH 子句：用于定义一个或多个临时命名的结果集，这些结果集仅在当前 SQL 语句执行期间有效。
• AS (...)：括号内的子查询负责生成临时结果集的数据。
• 主查询：在 WITH 子句之后，可以像使用普通表一样，通过名称引用这个临时结果集进行后续查询。

示例1：

示例2：

-- 创建业务表
CREATE TABLE IF NOT EXISTS users (
    id INT,          -- 用户ID
    name VARCHAR(10) -- 用户名
);

INSERT INTO users (id, name) VALUES (1, '小明'), (2, '小红');

-- 核心：最简CTE查询（仅筛选+查询）
WITH user_cte AS (
    -- CTE子查询：仅筛选id=1的用户，只查name字段
    SELECT name FROM users WHERE id = 1
)
-- 主查询：直接查CTE的结果（无任何统计/计算）
SELECT * FROM user_cte;

返回结果如下：

 name
-------
 小明
(1 row)

多CTE

多 CTE（Multiple Common Table Expressions）指的是在同一个WITH子句中 定义两个或以上的临时结果集（CTE），这些 CTE 共享同一个查询上下文，可独立使用也可相互依赖，本质是把多个独立的临时数据集 “打包” 在一次查询中，让复杂逻辑更清晰。

语法：

WITH
  cte_name1 AS (子查询1),  -- 第一个CTE：末尾必须加英文逗号
  cte_name2 AS (子查询2),  -- 中间CTE：依次加逗号
  cte_name3 AS (子查询3)   -- 最后一个CTE：末尾无逗号
-- 主查询：可关联/合并所有CTE结果
SELECT * FROM cte_name1 JOIN cte_name2 ON 关联条件;

示例：

CREATE TABLE IF NOT EXISTS products (
    id INT,          -- 商品ID
    name VARCHAR(20),-- 商品名
    price DECIMAL(8,2), -- 价格
    category VARCHAR(10) -- 分类
);
INSERT INTO products VALUES
(1, 'T恤', 59.9, '服饰'),
(2, '牛仔裤', 129.9, '服饰'),
(3, '保温杯', 89.9, '日用品'),
(4, '毛巾', 19.9, '日用品');

-- 多CTE核心逻辑：分别统计不同分类的商品数
WITH
  -- CTE1：统计服饰类商品数量
  clothing_count AS (
    SELECT COUNT(*) AS cloth_num FROM products WHERE category = '服饰'
  ),
  -- CTE2：统计日用品类商品数量（和CTE1无依赖）
  daily_count AS (
    SELECT COUNT(*) AS daily_num FROM products WHERE category = '日用品'
  )
-- 主查询：合并两个CTE的结果
SELECT cloth_num, daily_num FROM clothing_count, daily_count;

返回结果如下：

cloth_num | daily_num
----------|----------
2         | 2
(1 row)

递归CTE

递归 CTE 是 CTE 的进阶形态，通过RECURSIVE关键字实现自引用查询，专门用于处理层级 / 树形结构数据（如组织架构、商品分类、评论楼中楼）或序列生成（如生成连续日期 / 数字）。

它的核心是 “递推” 思想：

• 锚点（Anchor）：定义查询的起始点（基础数据，非递归部分）；
• 递归体（Recursive Term）：引用自身，基于锚点数据迭代查询，直到无新数据返回；
• 终止条件：无需显式定义，当递归体查询返回空结果时，自动终止。

语法：

WITH RECURSIVE cte_name (列名1, 列名2, ...) AS (
    -- 1. 锚点成员：初始查询（非递归，必须有且无自引用）
    SELECT 列1, 列2 FROM 表名 WHERE 初始条件
    UNION ALL  -- 连接锚点和递归体（常用UNION ALL，避免去重损耗性能）
    -- 2. 递归成员：引用CTE自身，实现迭代
    SELECT t.列1, t.列2 
    FROM 原表 t
    JOIN cte_name r ON t.关联列 = r.关联列  -- 核心：原表与CTE自身关联
    WHERE 递归过滤条件  -- 可选，限制递归范围
)
-- 3. 主查询：读取递归结果
SELECT * FROM cte_name;

递归 CTE 的执行原理：

递归 CTE 的执行过程像 “循环迭代”，步骤如下：

1. 先执行锚点成员，生成第一批结果，存入临时结果集；
2. 执行递归成员：用临时结果集的数据关联原表，生成新结果；
3. 若新结果非空，将其加入临时结果集，重复步骤 2；若为空，终止递归；
4. 主查询读取最终的临时结果集，返回所有数据。

示例：

-- 递归CTE：生成1到5的连续数字
WITH RECURSIVE num_cte AS (
    -- 锚点：起始数字1
    SELECT 1 AS num
    UNION ALL
    -- 递归：数字+1，直到5
    SELECT num + 1 FROM num_cte WHERE num < 5
)
SELECT num FROM num_cte;

返回结果如下：

 num
-----
   1
   2
   3
   4
   5
(5 rows)