SQL实战：为customer_orders每条记录生成指定格式订单项

1. 配料ID匹配不到名称的解决方法 #

问题根源是拆分配料ID字符串时，未处理ID前后的空格（比如字段值为"4, 5, 6"，拆分后得到"4"、" 5"、" 6"，带空格的ID无法匹配pizza_toppings表的topping_id）。处理时需在拆分后用TRIM()去除空格，再转为整数关联表。

PostgreSQL示例 #

-- 处理排除配料的拆分与匹配
SELECT 
    co.order_id,
    co.pizza_id,
    pt.topping_name
FROM customer_orders co
LEFT JOIN UNNEST(STRING_TO_ARRAY(co.exclusions, ',')) ex(excl_id) ON TRUE
LEFT JOIN pizza_toppings pt ON TRIM(ex.excl_id)::INT = pt.topping_id
WHERE co.exclusions IS NOT NULL AND co.exclusions != '';

MySQL示例 #

WITH RECURSIVE split_exclusions AS (
    SELECT 
        order_id,
        pizza_id,
        TRIM(SUBSTRING_INDEX(exclusions, ',', 1)) AS excl_id,
        TRIM(SUBSTRING(exclusions, LOCATE(',', exclusions) + 1)) AS remaining
    FROM customer_orders
    WHERE exclusions IS NOT NULL AND exclusions != ''
    UNION ALL
    SELECT 
        order_id,
        pizza_id,
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)) AS excl_id,
        TRIM(SUBSTRING(remaining, LOCATE(',', remaining) + 1)) AS remaining
    FROM split_exclusions
    WHERE remaining != ''
)
SELECT 
    se.order_id,
    se.pizza_id,
    pt.topping_name
FROM split_exclusions se
LEFT JOIN pizza_toppings pt ON se.excl_id::INT = pt.topping_id;

2. 合并排除/额外配料生成指定格式 #

通过聚合函数拼接配料名称，再用CASE语句组合不同场景的订单项格式：

完整PostgreSQL示例 #

WITH order_toppings AS (
    -- 聚合排除配料列表
    SELECT 
        co.order_id,
        co.pizza_id,
        STRING_AGG(pt.topping_name, ', ') AS exclude_toppings
    FROM customer_orders co
    LEFT JOIN UNNEST(STRING_TO_ARRAY(co.exclusions, ',')) ex(excl_id) ON TRUE
    LEFT JOIN pizza_toppings pt ON TRIM(ex.excl_id)::INT = pt.topping_id
    GROUP BY co.order_id, co.pizza_id
    UNION ALL
    -- 聚合额外配料列表
    SELECT 
        co.order_id,
        co.pizza_id,
        STRING_AGG(pt.topping_name, ', ') AS extra_toppings
    FROM customer_orders co
    LEFT JOIN UNNEST(STRING_TO_ARRAY(co.extras, ',')) ex(extra_id) ON TRUE
    LEFT JOIN pizza_toppings pt ON TRIM(ex.extra_id)::INT = pt.topping_id
    GROUP BY co.order_id, co.pizza_id
),
order_topping_summary AS (
    -- 合并同一订单的排除/额外配料信息
    SELECT 
        order_id,
        pizza_id,
        MAX(exclude_toppings) AS exclude_toppings,
        MAX(extra_toppings) AS extra_toppings
    FROM order_toppings
    GROUP BY order_id, pizza_id
)
-- 生成最终订单项格式
SELECT 
    ots.order_id,
    pn.pizza_name || 
    CASE 
        WHEN ots.exclude_toppings IS NOT NULL AND ots.extra_toppings IS NOT NULL 
            THEN ' - Exclude: ' || ots.exclude_toppings || ' + Extra: ' || ots.extra_toppings
        WHEN ots.exclude_toppings IS NOT NULL 
            THEN ' - Exclude: ' || ots.exclude_toppings
        WHEN ots.extra_toppings IS NOT NULL 
            THEN ' + Extra: ' || ots.extra_toppings
        ELSE ''
    END AS order_item
FROM order_topping_summary ots
JOIN pizza_names pn ON ots.pizza_id = pn.pizza_id;

关键逻辑说明 #

• 先用CTE拆分并聚合每个订单的配料名称，确保ID匹配时去除空格并转换为正确类型
• 用MAX()将同一订单的排除、额外配料信息汇总到一行
• 通过CASE语句根据配料存在情况，拼接出纯披萨名、含排除配料、含额外配料、同时含两者四种格式的订单项

MySQL环境下只需替换聚合函数（用GROUP_CONCAT()替代STRING_AGG()），拆分逻辑改用递归CTE，核心逻辑保持一致。

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