与 MySQL 中的 Distinct 相反
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7411639/
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
Opposite of Distinct in MySQL
提问by seeTheObvious
I would like to know if there is an opposite of "select distinct" in sql ,so that i can use to get values from a table of only which has repeated multiple times.
我想知道在 sql 中是否存在与“select distinct”相反的内容，以便我可以使用仅从重复多次的表中获取值。
Thanks
谢谢
回答by Bohemian
select some_column, count(*)
from some_table
group by 1
having count(*) > 1;
On databases like mysql, you may even omit selecting count(*)to leave justthe column values:
像MySQL数据库，你甚至可以省略选择count(*)离开只是列值：
select some_column
from some_table
group by 1
having count(*) > 1;
回答by paxdiablo
You need a group bywith a havingclause, something like:
您需要一个group bywithhaving子句，例如：
select person, count(friends)
from people
group by person
having count(friends) > 1
This would give you a list of all people and their friend count, except for those poor sad soles who have been befriended by only their mothers or, worse yet, not eventheir mothers :-)
这会给你一个所有人的名单和他们的朋友数，除了那些只和他们的母亲交朋友的可怜的悲伤鞋底，或者更糟糕的是，甚至没有他们的母亲:-)
You have to use havinginstead of wherein this case, since the former filters after grouping while the latter filters before, and you don't have the information of aggregate functions like count()until aftergrouping has taken place.
你必须使用having，而不是where在这种情况下，因为前者过滤器之前，而后者过滤分组后，和你没有聚合函数的信息一样count()，直到之后的分组已经发生。
回答by Rene Wooller
I was looking for something like this for a many-to-many join situation where I want to show how many duplicates exist in both tables. count(distinct col) wasn't exactly the right solution because result of '1' indicated that the column indeed had duplicates, but didn't say how many. The 'opposite of distinct' I used for this was an inversion using count(*): (count(*)-count(distinct col)+1) and it did the job fine.
我正在为多对多连接情况寻找这样的东西，我想显示两个表中存在多少重复项。count(distinct col) 并不是正确的解决方案，因为 '1' 的结果表明该列确实有重复项，但没有说明有多少。我为此使用的“不同的相反”是使用 count(*): (count(*)-count(distinct col)+1) 的反演，它做得很好。
