英文:
why is this subquery not working? SQL Server begginer practice case
问题
生成顾客订单表中每个披萨订单的按字母顺序排列的逗号分隔的配料列表的SQL查询有些问题，您可以尝试以下更正：
SELECT
CONCAT(pizza_name, ': ',
STUFF(
(SELECT ', ' + topping_name
FROM pizza_recipes AS pr
CROSS APPLY STRING_SPLIT(pr.toppings, ',')
JOIN pizza_toppings AS pt ON pt.topping_id = TRY_CAST(value AS INT)
WHERE pr.pizza_id = co.pizza_id
ORDER BY topping_name
FOR XML PATH('')), 1, 2, '')
) AS pizza_with_toppings
FROM customer_orders AS co
JOIN pizza_names AS pn ON co.pizza_id = pn.pizza_id
此查询应该返回按字母顺序排列的每个披萨订单的逗号分隔的配料列表，例如："Meatlovers: Bacon, Beef, ... , Salami"。希望这可以帮助您解决问题。如果还有其他问题，请随时提出。
英文:
I have these tables:
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_names (
"pizza_id" INTEGER,
"pizza_name" VARCHAR
);
INSERT INTO pizza_names
("pizza_id", "pizza_name")
VALUES
(1, 'Meatlovers'),
(2, 'Vegetarian');
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');
And I'm asked: Generate an alphabetically ordered comma separated ingredient list for each pizza order from the customer_orders table.
- For example: "Meat Lovers: Bacon, Beef, ... , Salami"
So far I did this queries that separed work but when Im building it as a subquery is not working:
SELECT CONCAT(pizza_name + ':',
(SELECT toppings_per_recipe
FROM
(SELECT string_agg(topping_name, ', ') as toppings_per_recipe
FROM pizza_recipes as pr
cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt
ON topping_id = VALUE GROUP BY pr.pizza_id)as o))
FROM customer_orders AS co
JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id
when I run this it says 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'
but I don't know how to fix it and get the results I need (Im new in SQL so I'm a little lost here).
答案1
得分: 2
I think this is what you intended. When you write it to use a lookup then you'll need to restrict (correlate) to a single row. There was also another level of nesting that served no purpose:
要避免相关性，您可以使用生成长名称的派生表：
SELECT CONCAT(pizza_name, '：', toppings_per_recipe)
FROM customer_orders AS co JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id
JOIN (
SELECT pr.pizza_id,
string_agg(topping_name, '，') within group (order by topping_name) as toppings_per_recipe
FROM pizza_recipes as pr cross apply string_split(pr.toppings, '，')
JOIN pizza_toppings AS pt ON topping_id = VALUE
GROUP BY pr.pizza_id
) as pizza_desc ON pizza_desc.pizza_id = pn.pizza_id;
英文:
I think this is what you intended. When you write it to use a lookup then you'll need to restrict (correlate) to a single row. There was also another level of nesting that served no purpose:
SELECT CONCAT(pizza_name + ':',
(
SELECT string_agg(topping_name, ', ') within group (order by topping_name)
FROM pizza_recipes as pr cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt ON topping_id = VALUE
WHERE pr.pizza_id = pn.pizza_id
)
) as toppings_per_recipe
FROM customer_orders AS co JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id;
To avoid the correlation you could use a derived table that generates the long names:
SELECT CONCAT(pizza_name, ':', toppings_per_recipe)
FROM customer_orders AS co JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id
JOIN (
SELECT pr.pizza_id,
string_agg(topping_name, ', ') within group (order by topping_name) as toppings_per_recipe
FROM pizza_recipes as pr cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt ON topping_id = VALUE
GROUP BY pr.pizza_id
) as pizza_desc ON pizza_desc.pizza_id = pn.pizza_id;
答案2
得分: 1
你可以直接将名称添加到配料中。
SELECT CONCAT(pn.pizza_name, ': ', string_agg(topping_name, ', ')) as toppings_per_recipe
FROM pizza_recipes as pr
cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt
ON topping_id = VALUE
JOIN pizza_names AS pn ON pr."pizza_id" = pn.pizza_id
GROUP BY pn.pizza_id, pn.pizza_name
| toppings_per_recipe |
|---|
| Meatlovers: Bacon, BBQ Sauce, Beef, Cheese, Chicken, Mushrooms, Pepperoni, Salami |
| Vegetarian: Cheese, Mushrooms, Onions, Peppers, Tomatoes, Tomato Sauce |
[fiddle](https://dbfiddle.uk/psPvqJFJ)
<details>
<summary>英文:</summary>
you can add the name drie4ctly to the ingridients
SELECT CONCAT(pn.pizza_name,': ', string_agg(topping_name, ', ')) as toppings_per_recipe
FROM pizza_recipes as pr
cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt
ON topping_id = VALUE
JOIN pizza_names AS pn ON pr."pizza_id" = pn.pizza_id
GROUP BY pn.pizza_id,pn.pizza_name
| toppings\_per\_recipe |
| :-------------------|
| Meatlovers: Bacon, BBQ Sauce, Beef, Cheese, Chicken, Mushrooms, Pepperoni, Salami |
| Vegetarian: Cheese, Mushrooms, Onions, Peppers, Tomatoes, Tomato Sauce |
[fiddle](https://dbfiddle.uk/psPvqJFJ)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论