MySQL中用值替代表实现关系除法：查询可制作的鸡尾酒

我正在开发一个程序，用于根据用户手头的现有食材查询其可制作的鸡尾酒。数据库表结构如下：

• CocktailIngredients表：包含字段cocktailId、ingredientId，存储鸡尾酒与食材的关联关系；
• UserIngredients表：包含字段ingredientId，存储用户拥有的食材ID。

目前我使用关系除法的SQL查询实现需求，但不想将访客用户的食材存入数据库（避免写入循环），请问能否在查询中手动输入食材ID来替代UserIngredients表？

当前使用的SQL查询语句如下：

SELECT cocktailId 
FROM CocktailIngredients a
JOIN UserIngredients b ON a.ingredientId = b.ingredientId
GROUP BY cocktailId
HAVING COUNT(*) = (
    SELECT COUNT(*) FROM CocktailIngredients c
    WHERE c.cocktailId = a.cocktailId
)

解决方案 #

完全没问题，你可以用临时构造的食材ID集合（比如SQL的VALUES子句）直接替代UserIngredients表，把访客的食材ID手动传入查询即可，无需写入数据库。

修改后的SQL语句如下：

SELECT a.cocktailId 
FROM CocktailIngredients a
-- 用VALUES子句手动填入访客拥有的食材ID
JOIN (VALUES 
    (1), (3), (5) -- 这里替换成实际的访客食材ID列表，多个ID用逗号分隔
) AS b(ingredientId) ON a.ingredientId = b.ingredientId
GROUP BY a.cocktailId
HAVING COUNT(DISTINCT a.ingredientId) = (
    SELECT COUNT(DISTINCT c.ingredientId) 
    FROM CocktailIngredients c
    WHERE c.cocktailId = a.cocktailId
)

关键说明： #

• VALUES (1), (3), (5)是核心替换部分，你可以根据访客提交的食材ID动态生成这一段；
• 加上DISTINCT是为了防止同一食材在CocktailIngredients表中重复记录时，导致计数偏差；
• 这种写法全程不需要操作数据库写入，完美避开访客食材的存储问题。

内容的提问来源于stack exchange，提问作者TheOneTrueFives