mysql 计算列表中的不同元素

Question

在 recipie_ingredient 表中我有不同的成分。

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;

最终结果应该是allIngredient: 6 and have: 2. ( DB Fiddle link )

Answer 1

在您的数据中，您有 2 次成分 123 和 403，如果您只想计算一次，您应该使用该查询

SELECT
  COUNT(distinct(ri.rel_ingredient)) as allIngredient,
  COUNT(distinct(ri.rel_ingredient in (123,403))) have
FROM recipe_ingredient ri
GROUP BY ri.rel_recipe