MySQL 计算 DISTINCT 值的出现次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1346345/
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
Count the occurrences of DISTINCT values
提问by JimmyJ
I am trying to find a MySQL query that will find DISTINCT values in a particular field, count the number of occurrences of that value and then order the results by the count.
我试图找到一个 MySQL 查询，它将在特定字段中找到 DISTINCT 值，计算该值的出现次数，然后按计数对结果进行排序。
example db
示例数据库
id name
----- ------
1 Mark
2 Mike
3 Paul
4 Mike
5 Mike
6 John
7 Mark
expected result
预期结果
name count
----- -----
Mike 3
Mark 2
Paul 1
John 1
回答by Amber
SELECT name,COUNT(*) as count
FROM tablename
GROUP BY name
ORDER BY count DESC;
回答by Pascal MARTIN
What about something like this:
像这样的事情怎么样：
SELECT
name,
count(*) AS num
FROM
your_table
GROUP BY
name
ORDER BY
count(*)
DESC
You are selecting the name and the number of times it appears, but grouping by name so each name is selected only once.
您正在选择名称及其出现的次数，但按名称分组，因此每个名称仅被选择一次。
Finally, you order by the number of times in DESCending order, to have the most frequently appearing users come first.
最后，您按照 DESCending 顺序按次数排序，以使出现频率最高的用户排在第一位。
回答by Aerin
Just changed Amber's COUNT(*) to COUNT(1) for the better performance.
只是将 Amber 的 COUNT(*) 更改为 COUNT(1) 以获得更好的性能。
SELECT name, COUNT(1) as count
FROM tablename
GROUP BY name
ORDER BY count DESC;
