MySQL 选择一个字段的计数大于一的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3710483/
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
Select where count of one field is greater than one
提问by stevebot
I want to do something like this:
我想做这样的事情：
SELECT *
FROM db.table
WHERE COUNT(someField) > 1
How can I achieve this in MySql?
如何在 MySql 中实现这一点？
回答by OMG Ponies
Use the HAVING, not WHEREclause, for aggregate result comparison.
使用HAVING, notWHERE子句进行聚合结果比较。
Taking the query at face value:
以面值查询：
SELECT *
FROM db.table
HAVING COUNT(someField) > 1
Ideally, there should be a GROUP BYdefined for proper valuation in the HAVINGclause, but MySQL does allow hidden columns from the GROUP BY...
理想情况下，应该GROUP BY在HAVING子句中定义适当的估值，但MySQL 确实允许 GROUP BY...
Is this in preparation for a unique constraint on someField? Looks like it should be...
这是在为 上的唯一约束做准备someField吗？看起来应该是...
回答by dandy_sql
SELECT username, numb from(
Select username, count(username) as numb from customers GROUP BY username ) as my_table
WHERE numb > 3
回答by Bill Karwin
You can also do this with a self-join:
您也可以使用自联接来执行此操作：
SELECT t1.* FROM db.table t1
JOIN db.table t2 ON t1.someField = t2.someField AND t1.pk != t2.pk
回答by Nalan Madheswaran
Here you go:
干得好：
SELECT Field1, COUNT(Field1)
FROM Table1
GROUP BY Field1
HAVING COUNT(Field1) > 1
ORDER BY Field1 desc
回答by Martin Smith
One way
单程
SELECT t1.*
FROM db.table t1
WHERE exists
(SELECT *
FROM db.table t2
where t1.pk != t2.pk
and t1.someField = t2.someField)
回答by Brent Arias
As OMG Ponies stated, the having clause is what you are after. However, if you were hoping that you would get discrete rows instead of a summary (the "having" creates a summary) - it cannot be done in a single statement. You must use two statements in that case.
正如 OMG Ponies 所说的，have 子句是你所追求的。但是，如果您希望获得离散行而不是摘要（“拥有”创建摘要），则无法在单个语句中完成。在这种情况下，您必须使用两个语句。
回答by user4551254
I give an example up on Group By between two table in Sql:
我举了一个例子，关于 Sql 中两个表之间的分组依据：
Select cn.name,ct.name,count(ct.id) totalcity
from city ct left join country cn on ct.countryid = cn.id
Group By cn.name,ct.name
Having totalcity > 2
Select cn.name,ct.name,count(ct.id) totalcity
from city ct left join country cn on ct.countryid = cn.id
Group By cn.name,ct.name
Having totalcity > 2
回答by Maham Khan
For me, Not having a group by just returned empty result. So i guess having a group by for the having statement is pretty important
对我来说，没有一个组只是返回空结果。所以我想有一个小组来发表声明是非常重要的
回答by Jim Blanchard
It should also be mentioned that the "pk" should be a key field. The self-join
还应该提到“pk”应该是一个关键字段。自加入
SELECT t1.* FROM db.table t1
JOIN db.table t2 ON t1.someField = t2.someField AND t1.pk != t2.pk
by Bill Karwin give you all the records that are duplicates which is what I wanted. Because some have more than two, you can get the same record more than once. I wrote all to another table with the same fields to get rid of the same records by key fields suppression. I tried
by Bill Karwin 为您提供所有重复记录，这正是我想要的。因为有些有两个以上，您可以多次获得相同的记录。我将所有内容写入另一个具有相同字段的表，以通过关键字段抑制来摆脱相同的记录。我试过
SELECT * FROM db.table HAVING COUNT(someField) > 1
SELECT * FROM db.table HAVING COUNT(someField) > 1
above first. The data returned from it give only one of the duplicates, less than 1/2 of what this gives you but the count is good if that is all you want.
以上先。从它返回的数据只给出一个重复项，不到它给你的数据的 1/2，但如果这就是你想要的，那么计数就很好。
