mysql 计数重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3935078/
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
mysql count duplicates
提问by Novice
how can I count duplicates rows wihtin select statement
如何计算 select 语句中的重复行
here is my table
这是我的桌子
name food
A Apple
B Banana
A Banana
C Apple
Result should be like this:
结果应该是这样的：
name food count
A apple (2)
B Banana (1)
A Bananaa (2)
C Apple (1)
I need a third coloumn in result set which value will be count (Name)
我需要结果集中的第三个列，哪个值将被计数（名称）
Thanks in advance
提前致谢
回答by Daniel Vandersluis
It's unclear exactly what you want to do, so here's two possibilities.
目前还不清楚你到底想做什么，所以这里有两种可能性。
If you want to determine how many times the same nameand foodcombination occurs, you can use GROUP BYto group like records and COUNTto determine how many there are in the group:
如果要确定相同name和food组合出现的次数，可以使用GROUP BY对类似记录进行分组并COUNT确定该组中有多少条记录：
SELECT name, food, COUNT(*) AS count
FROM your_table_name
GROUP BY name, food
Alternately, if you want to retrieve how many times only the name duplicates, you'll need a subquery:
或者，如果您只想检索名称重复的次数，则需要一个子查询：
SELECT name, food,
(
SELECT COUNT(*)
FROM your_table_name
WHERE name = table_alias.name
) AS count
FROM your_table_name AS table_alias
The first query will return only one row per name/foodgroup, along with the count of how many records appear in the group. The second query will return all rows, with a count of how many rows have the same name in each row.
第一个查询将仅返回每个name/food组的一行，以及该组中出现的记录数。第二个查询将返回所有行，并计算每行中有多少行具有相同的名称。
回答by Marc B
SELECT name, food, COUNT(food) AS cnt
FROM table
GROUP BY name, food
HAVING (cnt > 1)
If you want the count of everything, not just the tuples with duplicates, then eliminate the HAVING line, as that'll filter out anything that doesn't have duplicates.
如果您想要所有内容的计数，而不仅仅是具有重复项的元组，则消除 HAVING 行，因为这将过滤掉任何没有重复项的内容。
