SQL查询max(),count() [英] SQL query max(), count()
问题描述
数据库模式看起来像
employee(employee_name,street,city)
works(employee_name,company_name,salary)
公司(company_name,城市)
管理(employee_name,manager_name)
employee(employee_name,street,city)
works(employee_name,company_name,salary)
company(company_name,city)
manages(employee_name,manager_name)
所需执行的查询是:
找到拥有最多员工的公司。
the query needed to do is:
find the company that has the most employees.
我可以通过查询找出最大计数:
I could find out the maximum count by the query:
SELECT max( cnt ) max_cnt
FROM (
SELECT count( employee_name ) cnt, company_name
FROM works
GROUP BY company_name
)w1;
但现在我找不到公司的名字。
But now I can't find out the name of the company. If anyone has some idea please share.
推荐答案
要获取包含最大值的整行,您可以使用 ORDER BY ... DESC LIMIT 1 而不是
MAX
:
To get the entire row containing the maximum value you can use ORDER BY ... DESC LIMIT 1
instead of MAX
:
SELECT company_name, cnt
FROM (
SELECT company_name, count(employee_name) AS cnt
FROM works
GROUP BY company_name
) w1
ORDER BY cnt DESC
LIMIT 1
这篇关于SQL查询max(),count()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!