问题描述
我必须计算存储在PostgreSQL表中的食物和用过的食物的成分：
I have to calculate food and ingredients of used food stored in PostgreSQL tables:
table1 'usedfood'
food food used used
code name qty meas
----------------------------------------------
10 spaghetti 3 pcs
156 mayonnaise 2 pcs
173 ketchup 1 pcs
172 bolognese sauce 2 pcs
173 ketchup 1 pcs
10 spaghetti 2 pcs
156 mayonnaise 1 pcs
table2 'ingredients'
food ingr. ingredient qty meas
code code name /1 in 1
----------------------------------------------
10 1256 spaghetti rinf 75 gramm
156 1144 salt 0.3 gramm
10 1144 salt 0.5 gramm
156 1140 fresh egg 50 gramm
172 1138 tomato 80 gramm
156 1139 mustard 5 gramm
172 1136 clove 1 gramm
156 1258 oil 120 gramm
172 1135 laurel 0.4 gramm
10 1258 oil 0.4 gramm
172 1130 corned beef 40 gramm
结果：
used
times code food/ingredient qty meas
----------------------------------------------
5 1256 spaghetti rinf 375 gramm
8 1258 oil 362 gramm
2 1138 tomato 160 gramm
3 1140 fresh egg 150 gramm
2 1130 corned beef 80 gramm
3 1139 mustard 15 gramm
8 1144 salt 3.4 gramm
2 1136 clove 2 gramm
2 1135 laurel 0.8 gramm
2 173 ketchup 2 pcs //haven't any ingredients
现在，我通过遍历table1并查询每一行的table2，然后添加结果，以此类推（使用C）来处理大型数据，这可能非常慢。
For now I do this by looping through table1 and queriying table2 for each row then adding results and so on (with C) what may be very slow on larger data.
表1包含食品代码，食品名称和使用量。
表2包含成分（乱序），其成分和代码表示一种食品的和平度，并且其中也出现了食品代码。
表1中的使用量应乘以表2中的数量，根据每个食谱中都应添加到成分代码的结果中。
因此，进入食物意大利面的所有成分行均以食物意大利面代码（10）开头。
没有任何食物的食物成分应使用表1中的数量计算并显示相同的名称。这实际上意味着它是最终产品（如啤酒瓶）。
这可能更复杂，但是我很想问。
例如，在ingredinets列表中，可能是他自己配制的成分。例如芥末中含有醋，盐，种子等。然后呢？
在所示示例芥末的表2中，将其用作成品（组件）。
Table1 contains food code, food name and used quantity.Table2 contains ingredients (in messy order) with code and used quantity for one peace of food and also code of food in which appears.
Used quantity from table1 should be multiplied with quantity from table2 according to every recipe and should be added to result of ingredients code.
So all ingredient rows which goes to food "spaghetti" starts with food spaghetti code (10).
Food without any ingredient should be calculated with quantity from table1 and showed with same name. That actually mean it is a final product (like beer bottle).
Here may be more complication but I'm, affraid to ask.For example in ingredinets list may be ingredient which is recipe by himself. For example mustard which contains from vinegar, salt, seed, etc... What then?In table2 of showed example mustard is used as ready product (component).
是否可以通过仅使用PostgreSQL即可快速进行计算并获得结果的任何方法？PostgreSQL可以为C程序提供现成的结果吗？
也许不是似乎对我来说很复杂？该查询的外观如何？
Is here any way for do such calculation and getting results fast with using just PostgreSQL which will give ready results to C program?
Maybe not too complex as seem's for me? How would that query look like?
推荐答案
尝试
SELECT SUM(f.qty) used_times,
COALESCE(i.ingr_code, f.food_code) code,
COALESCE(i.name, f.name) name,
SUM(COALESCE(i.qty, 1) * f.qty) qty,
COALESCE(i.meas, f.meas) meas
FROM usedfood f LEFT JOIN ingredients i
ON f.food_code = i.food_code
GROUP BY i.ingr_code, i.name
输出：
| USED_TIMES | CODE | NAME | QTY | MEAS |
----------------------------------------------------
| 2 | 173 | ketchup | 2 | pcs |
| 2 | 1130 | corned beef | 80 | gramm |
| 2 | 1135 | laurel | 0.8 | gramm |
| 2 | 1136 | clove | 2 | gramm |
| 2 | 1138 | tomato | 160 | gramm |
| 3 | 1139 | mustard | 15 | gramm |
| 3 | 1140 | fresh egg | 150 | gramm |
| 8 | 1144 | salt | 3.4 | gramm |
| 5 | 1256 | spaghetti rinf | 375 | gramm |
| 8 | 1258 | oil | 362 | gramm |
这里是 演示
Here is SQLFiddle demo
这篇关于PostgreSQL，用于通过配方计算成分的复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！