mysql - MySQL通过临时表循环

我正在尝试将查询结果设置为变量，然后在另一个查询中使用该结果。

所以现在我在成分表中有几种类型的威士忌酒，我可以找到所有它们，并且具有：

CREATE TEMPORARY TABLE TempTable (Ingredient_Id int);
Insert into TempTable Select Ingredient_Id from ingredients where INSTR(Ingredient_Name,'Whiskey')>0;

这给了我所有我需要的身份证。然后我尝试通过以下方式获取相关数据：

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
where drink_recipes.Ingredient_Id in TempTable #This is the problem
Order By Drink_Name

我不知道如何使用TempTable中的每个ID运行此查询

最佳答案

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
where drink_recipes.Ingredient_Id in
(
  Select Ingredient_Id
  from ingredients
  where INSTR(Ingredient_Name,'Whiskey')>0
)
Order By Drink_Name

或者你也可以尝试

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
                      AND INSTR(ingredients.Ingredient_Name,'Whiskey') > 0
Order By Drink_Name

关于mysql - MySQL通过临时表循环，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18035911/