SQL Server子查询报错求助：生成披萨订单配料列表失败

错误原因 #

你遇到的报错是因为子查询没有和主查询的pizza_id关联，导致主查询每行执行时，子查询都会返回所有披萨的配料列表（两条数据），而SQL Server不允许在表达式位置使用返回多行的子查询，因此触发该错误。

另外，你的原代码只处理了披萨的基础配料，没有考虑订单中的exclusions（排除配料）和extras（额外添加配料），如果需求需要生成实际订单的最终配料列表，需要额外处理这两个字段。

解决方案 #

方案1：仅生成基础配料列表（不考虑排除/额外）

如果只需要披萨的标准配料，可先预生成每个披萨的按字母排序的配料字符串，再关联订单表拼接结果：

WITH pizza_base_toppings AS (
    SELECT 
        pr.pizza_id,
        -- 按字母排序后拼接配料名称
        STRING_AGG(pt.topping_name, ', ') WITHIN GROUP (ORDER BY pt.topping_name) AS base_toppings
    FROM pizza_recipes pr
    -- 拆分披萨配方中的配料ID字符串
    CROSS APPLY STRING_SPLIT(pr.toppings, ',') AS split_toppings
    -- 关联配料表获取名称，注意转换ID类型并处理空格
    JOIN pizza_toppings pt ON TRY_CAST(LTRIM(split_toppings.value) AS INT) = pt.topping_id
    GROUP BY pr.pizza_id
)
SELECT 
    CONCAT(pn.pizza_name, ': ', pbt.base_toppings) AS pizza_order_toppings
FROM customer_orders co
JOIN pizza_names pn ON co.pizza_id = pn.pizza_id
JOIN pizza_base_toppings pbt ON co.pizza_id = pbt.pizza_id
ORDER BY co.order_id;

方案2：生成包含排除/额外配料的最终订单配料列表

如果需要根据订单的exclusions和extras调整配料，完整SQL如下：

WITH pizza_base_toppings AS (
    -- 生成每个披萨的基础配料（按字母排序）
    SELECT 
        pr.pizza_id,
        STRING_AGG(pt.topping_name, ', ') WITHIN GROUP (ORDER BY pt.topping_name) AS base_toppings
    FROM pizza_recipes pr
    CROSS APPLY STRING_SPLIT(pr.toppings, ',') AS split_toppings
    JOIN pizza_toppings pt ON TRY_CAST(LTRIM(split_toppings.value) AS INT) = pt.topping_id
    GROUP BY pr.pizza_id
),
order_exclusions AS (
    -- 拆分每个订单的排除配料ID
    SELECT 
        co.order_id,
        co.pizza_id,
        TRY_CAST(LTRIM(split_exclusions.value) AS INT) AS exclusion_id
    FROM customer_orders co
    CROSS APPLY STRING_SPLIT(ISNULL(co.exclusions, ''), ',') AS split_exclusions
    WHERE TRY_CAST(LTRIM(split_exclusions.value) AS INT) IS NOT NULL
),
order_extras AS (
    -- 拆分每个订单的额外配料ID
    SELECT 
        co.order_id,
        co.pizza_id,
        TRY_CAST(LTRIM(split_extras.value) AS INT) AS extra_id
    FROM customer_orders co
    CROSS APPLY STRING_SPLIT(ISNULL(co.extras, ''), ',') AS split_extras
    WHERE TRY_CAST(LTRIM(split_extras.value) AS INT) IS NOT NULL
),
order_final_toppings AS (
    -- 计算每个订单的最终配料：基础配料 - 排除配料 + 额外配料
    SELECT 
        co.order_id,
        co.pizza_id,
        -- 去重并按字母排序拼接最终配料
        STRING_AGG(DISTINCT topping_name, ', ') WITHIN GROUP (ORDER BY topping_name) AS final_toppings
    FROM customer_orders co
    JOIN pizza_base_toppings pbt ON co.pizza_id = pbt.pizza_id
    -- 拆分基础配料为单个名称，方便筛选排除项
    CROSS APPLY STRING_SPLIT(pbt.base_toppings, ', ') AS base_split
    JOIN pizza_toppings pt_base ON pt_base.topping_name = base_split.value
    -- 左连接排除表，筛选未被排除的基础配料
    LEFT JOIN order_exclusions oe ON co.order_id = oe.order_id 
        AND co.pizza_id = oe.pizza_id 
        AND pt_base.topping_id = oe.exclusion_id
    WHERE oe.exclusion_id IS NULL
    -- 合并额外配料
    UNION ALL
    SELECT 
        co.order_id,
        co.pizza_id,
        pt_extra.topping_name
    FROM customer_orders co
    JOIN order_extras oe ON co.order_id = oe.order_id 
        AND co.pizza_id = oe.pizza_id
    JOIN pizza_toppings pt_extra ON pt_extra.topping_id = oe.extra_id
    GROUP BY co.order_id, co.pizza_id
)
-- 拼接披萨名称和最终配料列表
SELECT 
    CONCAT(pn.pizza_name, ': ', oft.final_toppings) AS pizza_order_toppings,
    co.order_id,
    co.customer_id,
    co.order_time
FROM customer_orders co
JOIN order_final_toppings oft ON co.order_id = oft.order_id 
    AND co.pizza_id = oft.pizza_id
JOIN pizza_names pn ON co.pizza_id = pn.pizza_id
ORDER BY co.order_id, co.pizza_id;

关键说明 #

• 使用STRING_SPLIT拆分逗号分隔的ID字符串，注意用LTRIM处理ID中的空格
• 用STRING_AGG(...) WITHIN GROUP (ORDER BY ...)实现按字母排序的字符串拼接
• 通过CTE分步处理基础配料、排除项、额外项，逻辑更清晰，避免子查询返回多行的问题

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