SQL ... 其中 count(col) > 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1804731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
... where count(col) > 1
提问by cimnine
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?
有任何想法吗？
回答by Donnie
Use the havingclause for comparing aggregates.
使用having子句比较聚合。
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?
此外，您需要根据您聚合的内容进行分组，以便查询正常工作。以下是一个开始，但由于您缺少 group by 子句，因此它仍然无法正常工作。你到底想数什么？
select fk, count(value)
from table
group by fk
having count(value) > 1;
