... where count（col）> 1 [英] ... where count(col) > 1

问题描述

我有这样一个表：

  + ----- + ----- + --- ---- + 
 | id | fk |值| 
 + ----- + ----- + ------- + 
 | 0 | 1 |彼得| 
 | 1 | 1 |乔什| 
 | 3 | 2 | marc | 
 | ... | ... | ... | 
  

现在我想要获取具有多个值的所有条目。
预期的结果是：

  + ----- + ------- + 
 | fk | count | 
 + ----- + ------- + 
 | 1 | 2 | 
 | ... | ... | 
  

我试图达到这样的效果：

 从表中选择fk，count（value）where count（value）> 1; 
  

但Oracle不喜欢它。

<所以我试了这个...

pre $ select $ from
select fk，count（value）as cnt从表
）其中cnt> 1;

...没有成功。

任何想法？

解决方案使用具有子句来比较聚合。

另外，您需要根据您正在聚合的内容进行分组，以使查询正常工作。以下是一个开始，但由于您错过了group by子句，它仍然不起作用。

 从表
中选择fk，count（value）
由fk 
计数（值）> 1; 
  

I have a table like this:

+-----+-----+-------+
| id  | fk  | value |
+-----+-----+-------+
| 0   | 1   | peter |
| 1   | 1   | josh  |
| 3   | 2   | marc  |
| ... | ... | ...   |

I'd like now to get all entries which have more than one value. The expected result would be:

+-----+-------+
| fk  | count |
+-----+-------+
| 1   | 2     |
| ... | ...   |

I tried to achieve that like this:

select fk, count(value) from table where count(value) > 1;

But Oracle didn't like it.

So I tried this...

select * from (
    select fk, count(value) as cnt from table
) where cnt > 1;

...with no success.

Any ideas?

解决方案

Use the having clause for comparing aggregates.

Also, you need to group by what you're aggregating against for the query to work correctly. The following is a start, but since you're missing a group by clause still it won't quite work. What exactly are you trying to count?

select fk, count(value) 
from table 
group by fk
having count(value) > 1;

这篇关于... where count（col）&gt; 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！