在配方表中，我有不同的配料。
create table recipe_ingredient (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rel_recipe INT(6),
rel_ingredient INT(6)
);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 32);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 99);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 123);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 123);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 227);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 395);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 403);
INSERT INTO recipe_ingredient (rel_recipe, rel_ingredient) VALUES(1, 403);根据我的配料，我想得到由我的配料组成的食谱。我想在食谱中找到一些独特的配料。在db fiddle中，我创建了一个表并插入了演示数据，我还添加了当前无法正常工作的sql。
SELECT
COUNT(distinct(ri.rel_ingredient)) as allIngredient,
sum((ri.rel_ingredient) in (123,403)) have
FROM recipe_ingredient ri
GROUP BY ri.rel_recipe;最终结果应该是AllingCredit:6和have:2(数据库（小提琴链接）
2条答案
按热度按时间sycxhyv71#
在您的数据中有2个123和403，如果您只想计算它们一次，您应该使用该查询
dzhpxtsq2#
我准备了一个解决方案，但有人能检查一下这是不是最快的解决方案。检查这里的小提琴。