MySQL计数重复 [英] mysql count duplicates

问题描述

如何计算带有select语句的重复行

how can I count duplicates rows wihtin select statement

这是我的桌子

name food  
A    Apple  
B    Banana  
A    Banana  
C    Apple  

结果应如下所示:

name food count
A    apple (2)  
B    Banana (1)  
A    Bananaa (2)  
C    Apple (1)  

我需要结果集中的第三列，其值将为count(名称)

I need a third coloumn in result set which value will be count (Name)

预先感谢

推荐答案

SELECT name, food, COUNT(food) AS cnt
FROM table
GROUP BY name, food
HAVING (cnt > 1)

如果您想统计所有内容，而不仅仅是具有重复项的元组，则消除HAVING行，因为这会过滤掉没有重复项的任何内容.

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.

这篇关于MySQL计数重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！