求助：披萨配料总用量SQL查询错误排查及正确实现

现有如下数据表结构及数据：

CREATE TABLE customer_orders (
      "order_id" INT,
      "customer_id" INTEGER,
      "pizza_id" INTEGER,
      "exclusions" VARCHAR(4),
      "extras" VARCHAR(4),
      "order_time" DATETIME
    );
INSERT INTO customer_orders
  ("order_id", "customer_id", "pizza_id", "exclusions", "extras", "order_time")
VALUES
  ('1', '101', '1', '', '', '2020-01-01 18:05:02'),
  ('2', '101', '1', '', '', '2020-01-01 19:00:52'),
  ('3', '102', '1', '', '', '2020-01-02 23:51:23'),
  ('3', '102', '2', '', NULL, '2020-01-02 23:51:23'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '2', '4', '', '2020-01-04 13:23:46'),
  ('5', '104', '1', NULL, '1', '2020-01-08 21:00:29'),
  ('6', '101', '2', NULL, NULL, '2020-01-08 21:03:13'),
  ('7', '105', '2', NULL, '1', '2020-01-08 21:20:29'),
  ('8', '102', '1', NULL, NULL, '2020-01-09 23:54:33'),
  ('9', '103', '1', '4', '1, 5', '2020-01-10 11:22:59'),
  ('10', '104', '1', NULL, NULL, '2020-01-11 18:34:49'),
  ('10', '104', '1', '2, 6', '1, 4', '2020-01-11 18:34:49');

CREATE TABLE pizza_toppings (
  "topping_id" INTEGER,
  "topping_name" VARCHAR(50)
);
INSERT INTO pizza_toppings
  ("topping_id", "topping_name")
VALUES
  (1, 'Bacon'),
  (2, 'BBQ Sauce'),
  (3, 'Beef'),
  (4, 'Cheese'),
  (5, 'Chicken'),
  (6, 'Mushrooms'),
  (7, 'Onions'),
  (8, 'Pepperoni'),
  (9, 'Peppers'),
  (10, 'Salami'),
  (11, 'Tomatoes'),
  (12, 'Tomato Sauce');

CREATE TABLE pizza_recipes (
  "pizza_id" INTEGER,
  "toppings" VARCHAR
);
INSERT INTO pizza_recipes
  ("pizza_id", "toppings")
VALUES
  (1, '1, 2, 3, 4, 5, 6, 8, 10'),
  (2, '4, 6, 7, 9, 11, 12');

需求：统计所有已交付披萨的每种配料总用量，按使用频率从高到低排序。

编写的SQL语句出现错误（例如order 1中显示了order 10的exclusions值）：

select  order_id, extras_split, toppings_split, exclusions_split
from
        (SELECT value as extras_split, order_id, pizza_id 
        FROM customer_orders 
        cross apply string_split(extras, ','))as a
        JOIN (
        SELECT value as toppings_split, pizza_id
        FROM pizza_recipes cross apply string_split(toppings, ',')) as b
        ON a.pizza_id = b.pizza_id
        JOIN (
        SELECT value as exclusions_split, pizza_id
        FROM customer_orders 
        cross apply string_split(exclusions, ',')) as c
        ON b.pizza_id = c.pizza_id
group by  order_id, extras_split, toppings_split, exclusions_split
order by order_id

需要帮助：

1. 排查上述SQL语句的错误原因；
2. 实现正确的统计逻辑：基础配料（pizza_recipes.toppings）+额外配料（customer_orders.extras）-排除配料（customer_orders.exclusions）；
3. 关联pizza_toppings表获取配料名称；

预期最终结果示例：

1. 错误原因分析 #

你的SQL存在两个核心问题：

• 关联逻辑缺失：仅用pizza_id关联三个子查询，未绑定order_id，导致不同订单的配料、排除项、额外项被错误匹配。比如order 1和order 10都是pizza_id=1，会被错误关联到一起，出现跨订单的排除项。
• 空值处理遗漏：extras或exclusions为空字符串/NULL时，string_split不会返回行，导致这些订单的基础配料被遗漏，同时关联时会过滤掉没有额外/排除项的记录。

2. 正确SQL实现 #

以下SQL基于SQL Server语法，实现正确的统计逻辑：

WITH all_toppings AS (
    -- 提取每个订单披萨的基础配料
    SELECT 
        co.order_id,
        TRIM(pr.topping) AS topping_id
    FROM customer_orders co
    JOIN (
        SELECT 
            pizza_id,
            value AS topping
        FROM pizza_recipes
        CROSS APPLY STRING_SPLIT(toppings, ',')
    ) pr ON co.pizza_id = pr.pizza_id

    UNION ALL

    -- 提取每个订单披萨的额外配料，过滤空值
    SELECT 
        co.order_id,
        TRIM(ce.extra) AS topping_id
    FROM customer_orders co
    CROSS APPLY STRING_SPLIT(ISNULL(co.extras, ''), ',') ce
    WHERE TRIM(ce.extra) != ''

    EXCEPT

    -- 排除每个订单披萨的移除配料，过滤空值
    SELECT 
        co.order_id,
        TRIM(ce.exclusion) AS topping_id
    FROM customer_orders co
    CROSS APPLY STRING_SPLIT(ISNULL(co.exclusions, ''), ',') ce
    WHERE TRIM(ce.exclusion) != ''
)
SELECT 
    pt.topping_name AS 配料名称,
    COUNT(at.topping_id) AS 用量
FROM all_toppings at
JOIN pizza_toppings pt ON at.topping_id = CAST(pt.topping_id AS VARCHAR)
GROUP BY pt.topping_name
ORDER BY 用量 DESC;

3. 逻辑说明 #

• CTEall_toppings：
  1. 基础配料：关联customer_orders和拆分后的pizza_recipes，获取每个披萨的默认配料。
  2. 额外配料：拆分extras字段，过滤空值后添加到总配料列表。
  3. 排除配料：拆分exclusions字段，过滤空值后从总配料列表中移除对应项。
• 最终统计：关联pizza_toppings获取配料名称，按名称分组统计用量，最后按用量降序排序。

执行结果 #

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