MySQL 如何使用mysql找到最大计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16441032/
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
How to find the maximum count using mysql?
提问by user2225190
Please let me know what is wrong with the below command
请让我知道以下命令有什么问题
mysql> select max(count(*)) from emp1 group by name;
ERROR 1111 (HY000): Invalid use of group function
回答by matsko
Try:
尝试：
SELECT NAME, COUNT(*) as c FROM table GROUP BY name ORDER BY c DESC LIMIT 1
SELECT NAME, COUNT(*) as c FROM table GROUP BY name ORDER BY c DESC LIMIT 1
回答by WonderWorker
From the supplied code I understand that you wish to select the highest number of employees that share the same name.
从提供的代码中，我了解到您希望选择最多的同名员工。
The problem with your query is that you are trying to apply more than one level of aggregation in a single scope.
您的查询的问题在于您试图在单个范围内应用多个级别的聚合。
Try this:
尝试这个：
SELECT MAX(Total) FROM (SELECT COUNT(*) AS Total FROM emp1 GROUP BY name) AS Results
...or this:
...或这个：
SELECT COUNT(name) FROM emp1 GROUP BY name ORDER BY COUNT(name) DESC LIMIT 1
Both queries return the same result, but their implementations are different.
两个查询返回相同的结果，但它们的实现不同。
Use whichever is the fastest for you or whichever you prefer.
使用对您来说最快的或您喜欢的任何一个。
回答by Thorsten Dittmar
I'd to the following (assuming I understand correctly what you want):
我将执行以下操作（假设我正确理解您想要的内容）：
select c from
(
select count(*) as c, name from emp1 group by name
) tmp
order by c desc limit 1
This selects the largest count from all counts by name. For example, if your table contains
这将按名称从所有计数中选择最大的计数。例如，如果您的表包含
Name
-----------------------
Test
Test
Hello
World
World
World
The inner select would create a "table" with this data
内部选择将使用此数据创建一个“表”
c Name
----------------------
2 Test
1 Hello
3 World
The outer select would order this by cdescending and select the first entry, which is 3.
外部选择将按c降序排列并选择第一个条目，即3.
This can be shortened to
这可以缩短为
select count(*) c from emp1 group by name order by c desc limit 1
回答by Bohemian
You must select nameto group by it, then use max()on the result of that as a subquery:
您必须选择name按它分组，然后将max()其结果用作子查询：
select max(count)
from (
select
name,
count(*) as count
from emp1
group by name) x
I have formatted the query so you can see what's happening, rather than put it all on one line as you showed it. Btw the "x" at he fnf is a required alias for the subquery.
我已经格式化了查询，以便您可以看到发生了什么，而不是像您显示的那样将其全部放在一行中。顺便说一句， fnf 处的“x”是子查询所需的别名。
回答by Gordon Linoff
You are asking "what is wrong with your statement". This is your statement:
你在问“你的陈述有什么问题”。这是你的陈述：
select max(count(*))
from emp1
group by name;
Iunderstand what you mean. But a SQL Compiler does not. The reason is simple. A given selectcan have only one group byclause. And your query is asking for two of them. The first is the group byname. The second is an aggregation on all those results.
我明白你的意思。但是 SQL 编译器没有。原因很简单。一个给定select只能有一个group by子句。您的查询要求其中两个。第一个是group by名字。第二个是对所有这些结果的汇总。
The proper way to write your query (as you seem to intend) is using a subquery:
编写查询的正确方法（如您所愿）是使用子查询：
select max(cnt)
from (select count(*) as cnt
from emp1
group by name
) t
This is a perfectly reasonable solution that only uses standard SQL. Other answers have proposed the solution using the limitclause, which may be a bit more efficient.
这是一个完全合理的解决方案，只使用标准 SQL。其他答案提出了使用该limit条款的解决方案，这可能会更有效一些。
回答by Shaymer
Just include name in your select statement in order to use group by.
只需在选择语句中包含名称即可使用 group by。
Read about the Group By function here...enter link description here
在此处阅读分组依据功能...在此处输入链接描述
回答by Declan_K
SELECT MAX(name_count)
FROM
(
SELECT name
,count(*) as name_count
FROM emp1
GROUP BY
name
)
回答by kranthi kumar pulivarhty
***Example: 1***
SELECT *
FROM customer
WHERE customer.ID IN
(SELECT customer_id
FROM (SELECT customer_id, MAX(cust_count)
FROM (SELECT customer_id,
COUNT(customer_id)
AS cust_count
FROM `order`
GROUP BY customer_id) AS cust_count_tbl) AS cust_tbl);
***Example -2***
SELECT *
FROM customer
LEFT JOIN
(SELECT customer_id, COUNT(customer_id) AS cc
FROM `order`
GROUP BY customer_id
ORDER BY cc DESC
LIMIT 1) AS kk
ON customer.ID = kk.customer_id
WHERE kk.customer_id = customer.ID;
