PostgreSQL，混合食物和食谱 [英] PostgreSQL, mixed food and recipes
本文介绍了PostgreSQL，混合食物和食谱的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
不久之前我遇到了一个问题有待解决，但事实并非如此，我必须更好地解释它并提供更合适的表格:
DROP TABLE IF EXISTS usedfood;CREATE TABLE用过的食物(food_code int, food_name text, qtyu integer, meas text);INSERT INTO usedfood (food_code, food_name, qtyu, meas)值 (10, 'spaghetti', 3, 'pcs'),(156, '蛋黄酱', 2, 'pcs'),(173, '番茄酱', 1, 'pcs'),(172, '肉酱', 2, 'pcs'),(173, '番茄酱', 1, 'pcs'),(175, 'worchester 酱', 2, 'pcs'),(177, '帕尔马干酪', 1, '个'),(10, '意大利面', 2, 'pcs'),(156, '蛋黄酱', 1, 'pcs');如果存在成分，则删除表；创建表成分(food_code int, ingr_code int, ingr_name text, qtyi decimal(11,3), meas text);插入成分(food_code、ingr_code、ingr_name、qtyi、meas)值 (10, 1256, 'spaghetti rinf', 75, 'gramm'),(156, 1144, '盐', 0.3, '克'),(10, 1144, '盐', 0.5, '克'),(156, 1140, '鲜鸡蛋', 50, '克'),(172, 1138, '番茄', 80, '克'),(156, 1139, '芥末', 5, '克'),(172, 1136, '丁香', 1, '克'),(156, 1258, '油', 120, '克'),(172, 1135, '月桂', 0.4, '克'),(10, 1258, '油', 0.4, '克'),(172, 1130, '咸牛肉', 40, '克');这两个表格代表食物清单和某些食物中使用的成分清单.
然而，有些食物没有配料(这是一个问题).
此查询按食物列表计算和列出成分的使用，其中食物和成分通过food_code"逻辑连接:
SELECT SUM(f.qtyu) AS used_times,COALESCE(i.ingr_code, MAX(f.food_code)) AS 代码，COALESCE(i.ingr_name, MAX(f.food_name)) AS f_name,SUM(COALESCE(i.qtyi, 1) * f.qtyu)::decimal(11,3) AS 数量，COALESCE(max(i.meas), max(f.meas)) AS measFROM usedfood f LEFT JOIN 成分 iON f.food_code = i.food_codeGROUP BY i.ingr_code, i.ingr_name但是这里的问题是所有没有成分的食物都被组合在一起而不是按代码分组，并被列为一种食物.这是不想要的，我希望有人找到并解决该问题以从给定的表中获得此结果:
2 173 番茄酱 2.000 个1 175 巴马干酪 1.000 个2 177 伍斯特酱 2.000 件8 1144 盐 3.400 克3 1140 个新鲜鸡蛋 150.000 克2 1138 番茄 160.000 克8 1258 油 362.000 克2 1135 月桂树 0.800 克5 1256 意大利面 rinf 375.000 克2 1130 咸牛肉 80.000 克3 1139 芥末 15.000 克2 1136 丁香 2.000 克 解决方案
你在找这个吗?
SELECT SUM(f.qtyu) AS used_times,COALESCE(i.ingr_code, f.food_code) AS 代码，COALESCE(i.ingr_name, f.food_name) AS f_name,SUM(COALESCE(i.qtyi, 1) * f.qtyu)::decimal(11,3) AS 数量，COALESCE(i.meas, f.meas) AS measFROM usedfood f LEFT JOIN 成分 iON f.food_code = i.food_codeGROUP BY COALESCE(i.ingr_code, f.food_code),COALESCE(i.ingr_name, f.food_name),COALESCE(i.meas, f.meas)按代码排序；输出:
<前>|USED_TIMES |代码 |F_NAME |数量 |测量 ||------------|------|--------------------|-----|-------||2 |173 |番茄酱|2 |件 ||2 |175 |伍斯特酱|2 |件 ||1 |177 |帕尔马 |1 |件 ||2 |第1130章咸牛肉|80 |克 ||2 |第1135章月桂树 |0.8 |克 ||2 |第1136章丁香|2 |克 ||2 |第1138章番茄|160 |克 ||3 |第1139章芥末 |15 |克 ||3 |第1140章新鲜鸡蛋|150 |克 ||8 |第1144章盐|3.4 |克 ||5 |第1256章意大利面 rinf |第375话克 ||8 |第1258章油|第362话克 |这是SQLFiddle演示
Before some time I had a problem which seem's to be solved but it is not so I have to explain it better and support with more suitable tables:
DROP TABLE IF EXISTS usedfood;
CREATE TABLE usedfood
(food_code int, food_name text, qtyu integer, meas text);
INSERT INTO usedfood (food_code, food_name, qtyu, meas)
VALUES (10, 'spaghetti', 3, 'pcs'),
(156, 'mayonnaise', 2, 'pcs'),
(173, 'ketchup', 1, 'pcs'),
(172, 'bolognese sauce', 2, 'pcs'),
(173, 'ketchup', 1, 'pcs'),
(175, 'worchester sauce', 2, 'pcs'),
(177, 'parmesan', 1, 'pcs'),
(10, 'spaghetti', 2, 'pcs'),
(156, 'mayonnaise', 1, 'pcs');
DROP TABLE IF EXISTS ingredients;
CREATE TABLE ingredients
(food_code int, ingr_code int, ingr_name text, qtyi decimal(11,3), meas text);
INSERT INTO ingredients (food_code, ingr_code, ingr_name, qtyi, meas)
VALUES (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');
Those two tables represent a list of food and list of ingredients used in SOME food.
However, some food haven't ingredients (and that is a prolem).
This query count and list usage of ingredients by list of food where food and ingredients are logically connected by 'food_code':
SELECT SUM(f.qtyu) AS used_times,
COALESCE(i.ingr_code, MAX(f.food_code)) AS code,
COALESCE(i.ingr_name, MAX(f.food_name)) AS f_name,
SUM(COALESCE(i.qtyi, 1) * f.qtyu)::decimal(11,3) AS qty,
COALESCE(max(i.meas), max(f.meas)) AS meas
FROM usedfood f LEFT JOIN ingredients i
ON f.food_code = i.food_code
GROUP BY i.ingr_code, i.ingr_name
But here is issue that all food which haven't ingredients is grouped together instead of by code and is listed as one food. This is not wanted and I would like that some find and solve that problem to get this result from given tables:
2 173 ketchup 2.000 pcs
1 175 parmesan 1.000 pcs
2 177 worchester sauce 2.000 pcs
8 1144 salt 3.400 gramm
3 1140 fresh egg 150.000 gramm
2 1138 tomato 160.000 gramm
8 1258 oil 362.000 gramm
2 1135 laurel 0.800 gramm
5 1256 spaghetti rinf 375.000 gramm
2 1130 corned beef 80.000 gramm
3 1139 mustard 15.000 gramm
2 1136 clove 2.000 gramm
解决方案
Are you looking for this?
SELECT SUM(f.qtyu) AS used_times,
COALESCE(i.ingr_code, f.food_code) AS code,
COALESCE(i.ingr_name, f.food_name) AS f_name,
SUM(COALESCE(i.qtyi, 1) * f.qtyu)::decimal(11,3) AS qty,
COALESCE(i.meas, f.meas) AS meas
FROM usedfood f LEFT JOIN ingredients i
ON f.food_code = i.food_code
GROUP BY COALESCE(i.ingr_code, f.food_code),
COALESCE(i.ingr_name, f.food_name),
COALESCE(i.meas, f.meas)
ORDER BY code;
Output:
| USED_TIMES | CODE | F_NAME | QTY | MEAS | |------------|------|------------------|-----|-------| | 2 | 173 | ketchup | 2 | pcs | | 2 | 175 | worchester sauce | 2 | pcs | | 1 | 177 | parmesan | 1 | 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，混合食物和食谱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
