3种配料披萨SQL查询中递归CTE的内部执行机制解析

问题背景与表结构 #

在Datalemur平台练习SQL时，遇到一道计算3种配料披萨成本的问题，表结构和示例数据如下：

-- 创建表
CREATE TABLE pizza_toppings 
(
    topping_name VARCHAR2(255),
    ingredient_cost DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO pizza_toppings (topping_name, ingredient_cost) 
VALUES ('Pepperoni', 0.50);
INSERT INTO pizza_toppings (topping_name, ingredient_cost) 
VALUES ('Sausage', 0.70);
INSERT INTO pizza_toppings (topping_name, ingredient_cost) 
VALUES ('Chicken', 0.55);
INSERT INTO pizza_toppings (topping_name, ingredient_cost) 
VALUES ('Extra Cheese', 0.40);

-- 提交修改
COMMIT;

常规解法多采用CROSS JOIN，但有一个递归CTE的实现方案，这里拆解它生成配料组合的具体执行步骤。

递归CTE代码 #

-- 生成所有唯一的3配料组合及总成本
WITH topping_list AS 
(
    SELECT topping_name, ingredient_cost
    FROM pizza_toppings
),
combo_cte (pizza, total_cost, last_topping) AS 
(
    -- 锚点成员：以单个配料为起始
    SELECT 
        topping_name AS pizza,
        ingredient_cost AS total_cost,
        topping_name AS last_topping
    FROM 
        topping_list

    UNION ALL

    -- 递归成员：按字母顺序添加配料生成组合
    SELECT 
        c.pizza || ',' || t.topping_name AS pizza,
        c.total_cost + t.ingredient_cost AS total_cost,
        t.topping_name AS last_topping
    FROM 
        combo_cte c
    JOIN 
        topping_list t ON t.topping_name > c.last_topping  -- 确保组合唯一
)
-- 筛选仅含3种配料的组合并展示总成本
SELECT 
    pizza,
    ROUND(total_cost, 2) AS total_cost
FROM 
    (SELECT 
         pizza,
         total_cost,
         LENGTH(pizza) - LENGTH(REPLACE(pizza, ',', '')) + 1 AS topping_count
     FROM 
         combo_cte
    )
WHERE 
    topping_count = 3
ORDER BY 
    total_cost DESC;

执行步骤拆解 #

递归CTE分为锚点成员和递归成员两部分，数据库会循环执行递归成员直到没有新结果生成。

1. 锚点成员执行（第一轮） #

锚点成员从topping_list中取出所有单个配料，生成初始数据集：

这一步是递归的起点，所有后续组合都基于这些单个配料扩展。

2. 第一次递归执行（生成2配料组合） #

递归成员将锚点结果与topping_list关联，关联条件t.topping_name > c.last_topping确保只添加字母顺序在当前最后一个配料之后的配料，避免重复组合（比如不会同时生成Chicken,Pepperoni和Pepperoni,Chicken）。

执行后生成的2配料组合如下（完整共6条）：

• 从Chicken出发：关联Pepperoni、Sausage，得到Chicken,Pepperoni（总成本1.05）、Chicken,Sausage（总成本1.25）
• 从Extra Cheese出发：关联Chicken、Pepperoni、Sausage，得到Extra Cheese,Chicken（0.95）、Extra Cheese,Pepperoni（0.90）、Extra Cheese,Sausage（1.10）
• 从Pepperoni出发：关联Sausage，得到Pepperoni,Sausage（1.20）
• 从Sausage出发：无字母顺序在它之后的配料，无新结果

此时combo_cte的数据集包含所有1配料和2配料组合。

3. 第二次递归执行（生成3配料组合） #

这一轮用第一次递归生成的2配料组合作为输入，再次关联topping_list，同样遵循t.topping_name > c.last_topping的规则：

• 从Chicken,Pepperoni出发：关联Sausage，得到Chicken,Pepperoni,Sausage（总成本1.75）
• 从Chicken,Sausage出发：无符合条件的配料
• 从Extra Cheese,Chicken出发：关联Pepperoni、Sausage，得到Extra Cheese,Chicken,Pepperoni（1.45）、Extra Cheese,Chicken,Sausage（1.65）
• 从Extra Cheese,Pepperoni出发：关联Sausage，得到Extra Cheese,Pepperoni,Sausage（1.60）
• 从Extra Cheese,Sausage出发：无符合条件的配料
• 从Pepperoni,Sausage出发：无符合条件的配料

这一轮生成了所有4种3配料组合，此时递归没有新的结果可以生成（3配料组合之后无法再添加符合条件的配料），递归终止。

4. 筛选与排序 #

最后通过子查询计算每个组合的配料数量（用逗号数量+1判断），筛选出配料数量为3的记录，再按总成本降序排序，得到最终结果：

内容的提问来源于stack exchange，提问作者codeholic24