从MySQL中的字段中选择最常见的值

I have a table with a million rows, how do i select the most common(the value which appears most in the table) value from a field?

我有一个包含一百万行的表，如何从一个字段中选择最常见的（表中最多的值）值？

3 个解决方案

#1

You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears.

您需要按兴趣列进行分组，并为每个值选择值本身及其显示的行数。

Then it's a matter of sorting (to put the most common value first) and limiting the results to only one row.

然后是排序（将最常见的值放在第一位）并将结果限制为只有一行。

In query form:

在查询表格中：

SELECT column, COUNT(*) AS magnitude 
FROM table 
GROUP BY column 
ORDER BY magnitude DESC
LIMIT 1

#2

This thread should shed some light on your issue.

这个主题应该对你的问题有所了解。

Basically, use COUNT() with a GROUP BY clause:

基本上，使用COUNT（）和GROUP BY子句：

SELECT foo, COUNT(foo) AS fooCount 
FROM table
GROUP BY foo
ORDER BY COUNT(foo) DESC

And to get only the first result (most common), add

并且只获得第一个结果（最常见），添加

LIMIT 1

To the end of your query.

到查询结束。

#3

In case you don't need to return the frequency of the most common value, you could use:

如果您不需要返回最常用值的频率，您可以使用：

SELECT foo
FROM table
GROUP BY foo
ORDER BY COUNT(foo) DESC
LIMIT 1

This has the additional benefit of only returning one column and therefore working in subqueries.

这具有仅返回一列并因此在子查询中工作的额外好处。

#1