计数功能TO DISPLAY REPEATS [英] Count function TO DISPLAY REPEATS

问题描述

我有下表



配方名称成分

GOBIMANCHURI ONION

GOBIMANCHURI ONION
GOBIMANCHURI OIL

POORI POTATO

POORI马铃薯

POORI小麦

PONGAL RICE


我想写一个问题，所以它应该显示所有相同的食谱已经重复的食谱名称。在我上面的例子中我想要与成分名称一起显示GOBIMANCHURI以及POORI。我该怎么做？

解决方案

按名称和成分分组，然后按计数过滤。



这样的事情可能适合你：

 选择 recipe_name，成分 from  
（
  select  recipe_name，ingredient，count（*） as  [count] 来自 recipe  group  通过 recipe_name，成分
） as   source  
 其中 [count]>  1  

希望这会有所帮助，

Fredrik

使用 DISTINCT 子句，该子句指定结果集中只能显示唯一的行。



如果你想计算食谱，请试试这个：

  SELECT  RecipeName，Ingredient，Count（RecipeName） AS  CountOfRecipes 
  FROM  RecipeTable 
  GROUP   BY  RecipeName，Ingredient 

I have the following table

RECIPE NAME INGREDIENT
GOBIMANCHURI ONION
GOBIMANCHURI ONION
GOBIMANCHURI OIL
POORI POTATO
POORI POTATO
POORI WHEAT
PONGAL RICE

I WANT TO WRITE A QUERY SO THAT IT SHOULD DISPLAY ALL THE RECIPE NAME FOR WHICH THE SAME INGREDIENT HAS REPEATED MORE THAN ONCE. IN MY ABOVE EXAMPLE I WANT TO DISPLAY GOBIMANCHURI AS WELL AS POORI ALONG WITH THE INGREDIENT NAME. HOW CAN I DO THIS?

解决方案

Group by name and ingredient and then filter on count.

Something like this might do the trick for you:

select recipe_name, ingredient from
(
    select recipe_name, ingredient, count(*) as [count] from recipe group by recipe_name, ingredient
) as source
where [count] > 1

Hope this helps,
Fredrik

Use DISTINCT clause, which specifies that only unique rows can appear in the result set.

If you would like to count recipes, please try this:

SELECT RecipeName, Ingredient, Count(RecipeName) AS CountOfRecipes
FROM RecipeTable
GROUP BY RecipeName, Ingredient

这篇关于计数功能TO DISPLAY REPEATS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！