mysql - 从计数 SQL 中选择 MAX

Question

我在从数据库提取数据时遇到问题。我的代码如下所示:

SELECT MAX(maximum)as Number FROM 
(
SELECT department_name, COUNT(employees.employee_id) AS maximum
FROM departments, employees
WHERE departments.department_id=employees.department_id
GROUP BY department_name
)t

结果是:

数量

1   46

这是其中一个部门的最大员 worker 数。问题是我想要有一个额外的列，其中包含有 46 名员工的部门名称。我尝试过类似的事情:

select department_name, count(employees.employee_id)
from departments, employees
where departments.department_id=employees.department_id
group by department_name
having count(employees.employee_id) =
( SELECT MAX(maxx)FROM 
(SELECT department_name, COUNT(employees.employee_id) AS maxx
FROM departments, employees
WHERE departments.department_id=employees.department_id
GROUP BY department_name
);

但它不起作用。请帮忙!

Answer 1

试试这个

SELECT t.department_name, MAX(t.maximum) as Number FROM ( 
    SELECT 
        department_name, 
        COUNT(employees.employee_id) AS maximum 
    FROM departments, employees 
    WHERE departments.department_id=employees.department_id 
    GROUP BY department_name 
) t