MySQL 选择员工人数最多的部门

select department(s) with maximum number of employees

提问by Aditya

I have two tables EMP(id,name,DEPT_id)and DEPT(id ,name). I need to find the department(s) in which the maximum number of employees work. Please help.

我有两张桌子EMP(id,name,DEPT_id)和DEPT(id ,name). 我需要找到最多员工工作的部门。请帮忙。

采纳答案by zedfoxus

Just a little more verbose than the other two solutions, but it will get the job done...feel free to tweak to your convenience.

只是比其他两个解决方案更冗长一点，但它可以完成工作......随意调整以方便您。

select countbydept.*
from
(
  -- from EMP table, let's count number of records per dept
  -- and then sort it by count (highest to lowest)
  -- and take just the first value. We just care about the highest
  -- count
  select dept_id, count(*) as counter
  from emp
  group by dept_id
  order by counter desc
  limit 1
) as maxcount

inner join

(
  -- let's repeat the exercise, but this time let's join
  -- EMP and DEPT tables to get a full list of dept and 
  -- employe count
  select
    dept.id,
    dept.`name`,
    count(*) as numberofemployees
  from dept
  inner join emp on emp.dept_id = dept.id
  group by dept.id, dept.`name`
) countbydept 

-- combine the two queries's results by matching the employee count
on countbydept.numberofemployees = maxcount.counter

Example: http://sqlfiddle.com/#!9/7d6a2d/1

示例：http: //sqlfiddle.com/#!9/7d6a2d/1

回答by KTAnj

Try this query.

试试这个查询。

SELECT a.name,Max(a.NumEmp) AS maxEmpCount FROM ( SELECT d.name,COUNT(*) AS NumEmp FROM EMP e INNER JOIN DEPT d  ON e.DEPT_id = d.id GROUP BY e.DEPT_id ) AS a GROUP BY a.name

回答by mk2683

This will give the department name of the department which is having maximum number of employees.

这将给出拥有最大员工人数的部门的部门名称。

Select DEPT_NAME from department where DEPT_ID = (select DEPT_ID from (Select DEPT_ID, count(DEPT_ID) from Employee group by DEPT_ID order by count(DEPT_ID) desc) where rownum = 1);

回答by Omid Ziyaee

you can solve this using withstatement like this:

你可以使用这样的with语句来解决这个问题：

with deps as
 (select dep.department_name as dep_name, count(emp.employee_id) as cnt
    from departments dep
   inner join employees emp
      on emp.department_id = dep.department_id
   group by dep.department_name)
select deps.dep_name,cnt from deps 
where cnt=(select max(cnt) from deps)

OR

或者

select dep.department_name as dep_name, count(emp.employee_id) as cnt
  from departments dep
 inner join employees emp
    on emp.department_id = dep.department_id
 group by dep.department_name
having count(emp.employee_id) >= all (select count(emp.employee_id) as cnt
                                        from departments dep
                                       inner join employees emp
                                          on emp.department_id =
                                             dep.department_id
                                       group by dep.department_name)

OR

或者

with s1 as
 (select dep.department_name as dep_name,
         count(emp.employee_id) over(partition by dep.department_name) as cnt
    from departments dep
   inner join employees emp
      on emp.department_id = dep.department_id
   order by cnt desc),
s2 as
 (select s1.dep_name,
         s1.cnt,
         row_number() over(order by cnt desc) as row_num
    from s1)
select dep_name from s2 where row_num = 1

these solutions are proper for databases like Oracle that we do not have top(1)or limit 1

这些解决方案适用于我们没有的数据库，如 Oracletop(1)或limit 1

回答by Mahedi Sabuj

You can try this query.

你可以试试这个查询。

Select Id, Name from Dept
Where Id = (Select Top(1) DeptId from Emp 
            Group By DeptId
            order by Count(DeptId) desc)

回答by Abhishek Sahay

you can create view to find it.

你可以创建视图来找到它。

CREATE VIEW TEMP AS SELECT COUNT(EMP.id) AS A, DEPT.name AS B 
FROM EMP JOIN DEPT ON EMP.DEPT_id=DEPT.id GROUP BY DEPT.id;

SELECT MAX(A) FROM TEMP;

回答by sayaksan

Now, EMP(id,name,DEPT_id) and DEPT(id ,name) these two tables are given. Now, I insert some entries in the table in such a manner that:

现在，给出了 EMP(id,name,DEPT_id) 和 DEPT(id ,name) 这两个表。现在，我以如下方式在表中插入一些条目：

SELECT COUNT(*) AS NO_OF_EMPLOYEES,
       DEPARTMENT.DEPT_NAME
  FROM EMP, DEPARTMENT
 WHERE EMP.DEPT_ID=DEPARTMENT.DEPT_ID
 GROUP BY EMP.DEPT_ID
 ORDER BY NO_OF_EMPLOYEES;

This query generates the following:

此查询生成以下内容：

NO_OF_EMPLOYEES DEPT_NAME
3               Research
3               Finance
4               Sales
4               Product

Now, the query which gives the correct result:

现在，给出正确结果的查询：

SELECT COUNT(*) AS MAX_NO_OF_EMPLOYEES,
       DEPARTMENT.DEPT_NAME
  FROM EMP, DEPARTMENT
 WHERE EMP.DEPT_ID=DEPARTMENT.DEPT_ID
 GROUP BY EMP.DEPT_ID
HAVING MAX_NO_OF_EMPLOYEES=(
    SELECT COUNT(*) AS NO_OF_EMPLOYEES
      FROM EMP
     GROUP BY DEPT_ID
     ORDER BY NO_OF_EMPLOYEES DESC
     LIMIT 1
);

It will generate:

它将生成：

MAX_NO_OF_EMPLOYEES    DEPT_NAME
4                      Sales
4                      Product  

回答by Neeraj vishwakarma

This question can be solved in multiple ways

这个问题可以通过多种方式解决

• Using sub query

  SELECT name FROM dept WHERE id IN (SELECT dept_id FROM emp HAVING COUNT(dept_id) IN (SELECT MAX(COUNT(dept_id)) FROM emp) GROUP BY dept_id)
  

• Using Join

  SELECT name FROM emp e INNER JOIN dept d ON e. dept_id = d. id HAVING COUNT(e.dept_id) IN (SELECT MAX(COUNT(dept_id)) from emp) group by dept_id)
  

• 使用子查询

  SELECT name FROM dept WHERE id IN (SELECT dept_id FROM emp HAVING COUNT(dept_id) IN (SELECT MAX(COUNT(dept_id)) FROM emp) GROUP BY dept_id)
  

• 使用连接

  SELECT name FROM emp e INNER JOIN dept d ON e. dept_id = d. id HAVING COUNT(e.dept_id) IN (SELECT MAX(COUNT(dept_id)) from emp) group by dept_id)
  

回答by Yamini RV

SELECT department_id, count(employee_id) as 'No_of_Emp'
    FROM employees
    GROUP BY department_id
    ORDER BY No_of_Emp DESC