問題與無限連接到同一個表有關，直到存在但為查詢帶來了一個額外的表。表：
ingredients- 包含成分 ID 和名稱
ingredient_id|ingredient_name
1 |Water
2 |Salt
3 |Fancy Sauce
4 |Spices
5 |Pepper
6 |Chili
ingredients_to_ingredients- 包含每種成分的可選子成分
ingredient_id|mapped_ingredient_id
3 |1
3 |2
3 |4
4 |5
4 |6
meals_to_ingredients- 包含每餐的成分（可能包含子成分）
meal_id|mapped_ingredient_id
1 |1
2 |2
3 |4
為了獲得指定成分的子成分（如果有的話）（比如說#3 - 花式醬汁），我使用：
WITH RECURSIVE
cte AS ( SELECT *
FROM ingredients_to_ingredients
WHERE ingredient_id = 3
UNION ALL
SELECT ingredients_to_ingredients.*
FROM ingredients_to_ingredients
JOIN cte ON cte.mapped_ingredient_id = ingredients_to_ingredients.ingredient_id )
SELECT ingredients.ingredient_name
FROM cte
JOIN ingredients ON cte.mapped_ingredient_id = ingredients.ingredient_id;
并獲取串列：
Water
Salt
Spices
Pepper
Chili
作業演示 - https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=0303d2ae8cded77053d87a0f31d9c374
通常我需要獲取存盤在其他表中的多種成分的子成分 - meals_to_ingredients. 經過一天的重寫查詢，我能夠做到這一點：
- 查詢無限期地作業
- 查詢回傳錯誤
- 查詢回傳 0 條記錄
- 查詢絕對回傳所有記錄
How do I rewrite query so it returns ingredients (and sub-ingredients) of let's say two random meals? I can post links to my non-working examples of modified query at DB Fiddle if needed.
Edit: Updated with desired output. Let's say new query randomly selects meals #1 and #3.
Meal #1 has mapped ingredients #1, #2 (meals_to_ingredients table), and these ingredients have no sub-ingredients (no records in ingredients_to_ingredients table)
Meal #3 has mapped ingredient #3 (meals_to_ingredients table), and this ingredient has sub-ingredients #1, #2, #4 (ingredients_to_ingredients table), while sub-ingredient #4 itself has further sub-ingredients #5, #6 (ingredients_to_ingredients table again).
So the result of all ingredients should be:
Water (ingredient_id - 1)
Salt (ingredient_id - 2)
Fancy Sauce (ingredient_id - 3)
Water (ingredient_id - 1)
Salt (ingredient_id - 2)
Spices (ingredient_id - 4)
Pepper (ingredient_id - 5)
Chili (ingredient_id - 6)
In fact, I will only need a unique list of ingredients, but I have no problems writing a query which filters duplicated records, so the example result above includes all the ingredients for easier understanding how query should work.
uj5u.com熱心網友回復：
看這個：
WITH RECURSIVE
cte AS (
SELECT meal_id, mapped_ingredient_id ingredient_id
FROM meals_to_ingredients
UNION ALL
SELECT cte.meal_id, iti.mapped_ingredient_id
FROM cte
JOIN ingredients_to_ingredients iti USING (ingredient_id)
)
SELECT cte.meal_id, ingredient_id, i.ingredient_name
FROM cte
JOIN ingredients i USING (ingredient_id)
-- WHERE cte.meal_id IN (1, 3)
ORDER BY 1,2
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=ee26da089c9bb2e5046fea0bc29041ff
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/449613.html