SQL查询-查找数据库中所有配方中总共使用了4个或更多茶匙的成分的名称 [英] SQL query - Find the names of those ingredients of which we used a total of 4 or more teaspoons across all recipes in the database

问题描述

我已经坚持了一段时间。

I've been stuck on this for a while.

查找我们总共使用了4种或以上茶匙的那些成分的名称数据库中的所有食谱。

始终带有下划线的意思是此属性或这些属性是表的主键。斜体和粗体属性表示指向另一个表的外键。

我一直在尝试类似的方法。 ..

I've been trying things along the lines of...

SELECT name
FROM ingredient
JOIN recipe_ingredient ON ingredient.id = recipe_ingredient.ingredient_id
JOIN measurement ON recipe_ingredient.measurement_id = measurement.id
WHERE description = "teaspoon"
AND amount >4;

但是我不确定是否能得到正确的结果。我正在获取原料清单，但谁知道。

But I'm not entirely sure if I'm getting the correct result. I'm getting a list of ingredients but who knows.

任何指导将不胜感激。

结果看起来像-

NAME
盐
黑胡椒
盐
肉豆蔻
肉桂
酸橙果汁
黑胡椒粉

NAME Salt Black Pepper Salt Nutmeg Cinnamon Lime Juice Black Pepper

等...我知道我可以添加一些独特的东西来删除双打

etc etc... I know i can add distinct to remove the doubles

在我输入时在这里获取表格样本

Heres a sample of my table when i enter

SELECT *
FROM ingredient
JOIN recipe_ingredient ON ingredient.id = recipe_ingredient.ingredient_id
JOIN measurement ON recipe_ingredient.measurement_id = measurement.id

表格执行

推荐答案

我了解，我们正在寻找在所有食谱中至少使用4茶匙的配料，而不是使用4茶匙的食谱

I understand that we're looking for ingredients that were used at least 4 teaspoons in all recipies, not the recipes that used 4 teaspoons

SELECT i.name
FROM ingredient i
JOIN recipe_ingredient ri ON i.id = ri.ingredient_id
JOIN measurement m ON ri.measurement_id = m.id
WHERE m.description = "teaspoon"
group by i.name
having sum(ri.amount) >= 4;

这篇关于SQL查询-查找数据库中所有配方中总共使用了4个或更多茶匙的成分的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！