Sql 将表格和字符串连接以 for each 记录生成订单项目

我正在做一个练习 case ，我有这些表格:

    CREATE TABLE customer_orders (
      "order_id" INTEGER,
      "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_names (
  "pizza_id" INTEGER,
  "pizza_name" VARCHAR
);
INSERT INTO pizza_names
  ("pizza_id", "pizza_name")
VALUES
  (1, 'Meatlovers'),
  (2, 'Vegetarian');

我被问到: 以以下格式之一为Customers_Orders表中的每个记录生成订单项目:

• 肉食者
• 肉食者 - Exclude Beef
• 肉食者 - Extra Bacon
• 肉食者 - Exclude Cheese, Bacon - Extra Mushroom, Peppers

到目前为止，我已经能够做到这一点:

--for Extras
  SELECT concat(cast(pn.pizza_name as varchar), pt.topping_name ) as pizzas_plus_extras
FROM customer_orders as co
    CROSS APPLY string_split((extras), ',') 
full JOIN pizza_toppings as pt
ON VALUE in (cast(topping_id as varchar))
INNER JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id

--for exclusions

SELECT concat(cast(pn.pizza_name as varchar), pt.topping_name ) as pizzas_plus_exclusions
FROM customer_orders as co
    CROSS APPLY string_split((exclusions), ',') 
full JOIN pizza_toppings as pt
ON VALUE in (cast(topping_id as varchar))
INNER JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id

这给了我以下结果:

由于某种原因，它不想显示所有的记录(在额外的，它不会命名浇头编号5和4，同样的事情与排除编号6)，我有两个不同的表，我不知道如何连接和获得我被要求的结果.

我try 了String_agg添加一个分隔符，但我在每个表上都使用了它，所以，正如您所看到的，我仍然无法获得预期的结果.

如有任何帮助，我们不胜感激！