多个左连接和嵌套查询的SQL查询,Group_Concat
问题描述:
我在玩弄sql,在互联网上玩教程,在应用中使用一些基本的sqlite,并且遇到以下问题。多个左连接和嵌套查询的SQL查询,Group_Concat
我有几个表如下所述。
表1(配方)
ID, Name
1, Carrot Cake
2, Cheese Cake
3, Chocolate Cake
表2(方法)
ID, Method Name, RecipeID
1, Old School Method, 1
2, Low Salt Method, 1
3, Extra Chocolate, 3
表3(配料)
ID, Name, Quantity, MethodID
1, Eggs, 2, 1
2, Carrots, 1, 1
3, Flour, 40, 1
4, Salt, 2, 1
5, Eggs, 2, 2
6, Carrots, 1, 2
7, Flour, 50, 2
8, Milk, 20, 3
9, Chocolate, 10, 3
10, Eggs, 1, 3
我基本上想要做的是打印出配方列表,所有可能的方法以及这些方法中的所有成分。如果没有列出的贫困人群或方法,只显示空白或空白。
步骤1:
SELECT Recipe.* FROM Recipe
明显打印出
id name
1 Carrot Cake
2 Cheese Cake
3 Chocolate Cake
步骤2:
SELECT Recipe.*, GROUP_CONCAT(Method.Name) as Methods
FROM Recipe
LEFT JOIN Method
ON Method.RecipeID = Recipe.ID
GROUP BY Recipe.id
返回
id name Methods
1 Carrot Cake Low Salt Method,Old School Method
2 Cheese Cake (null)
3 Chocolate Cake Extra Chocolate
这又是有道理的,但是现在我想列出每种方法的成分和数量,像
1 Carrot Cake Low Salt Method (Eggs 2, Carrots 1, Flour 40, Salt 2), Old School Method (....)
格式还没有得到是相同的,但只显示同样的信息,不知何故
有一个快速谷歌,我碰到“嵌套查询”但是我努力使他们的工作
任何指导,将衷心感谢
感谢
答
您需要分两步分组数据:第一个成分,下一个方法。 这需要一个子查询:
select id, recipename, group_concat(name || ' ' || ingredients)
from (
select
r.id, r.recipename, m.name,
'(' || group_concat(i.name || ' ' || i.quantity, ',') || ')' ingredients
from recipe r
left join method m on m.recipeid = r.id
left join ingredients i on i.methodid = m.id
group by 1, 2, 3
) sub
group by 1, 2;
1 | Carrot Cake | Low Salt Method (Carrots 1, Eggs 2, Flour 50), Old School Method (Carrots 1, Eggs 2, Flour 40, Salt 2)
2 | Cheese Cake |
3 | Chocolate Cake| Extra Chocolate (Chocolate 10, Eggs 1, Milk 20)
为了更好地理解它是如何工作的,运行内部查询:
select
r.id, r.recipename, m.name,
'(' || group_concat(i.name || ' ' || i.quantity, ',') || ')' ingredients
from recipe r
left join method m on m.recipeid = r.id
left join ingredients i on i.methodid = m.id
group by 1, 2, 3;
1 | Carrot Cake | Low Salt Method | (Carrots 1, Eggs 2, Flour 50)
1 | Carrot Cake | Old School Method | (Carrots 1, Eggs 2, Flour 40, Salt 2)
2 | Cheese Cake | |
3 | Chocolate Cake| Extra Chocolate | (Chocolate 10, Eggs 1, Milk 20)
此结果集是一样的,你执行外查询的表。