Oracle的SQL练习：显示员工数最多的部门信息

题目：显示员工数最多的部门信息，显示部门ID、名称、部门员工数，部门的主管经理姓名*/

答案：

select

dept.department_id,dept.department_name,

count(*),e1.first_name||e1.last_name

from

departments dept,employees e  ,employees e1

where

dept.department_id=e.department_id

and

dept.manager_id=e1.employee_id

group by

dept.department_id,dept.department_name,

e1.first_name||e1.last_name

having count(*)=(select max(count(*)) from employees  group by department_id )

<1>/*第一步，查询出员工人数最多的部门的员工人数*/

select max(count(*)) from employees

group by department_id

<2>/*第二步，员工表和部门表连接，以部门分组，*/

select

dept.department_id,dept.department_name,

count(*),e1.first_name||e1.last_name

from

departments dept,employees e  ,employees e1

where

dept.department_id=e.department_id

and

dept.manager_id=e1.employee_id

group by

dept.department_id,dept.department_name,

e1.first_name||e1.last_name

<3>  /*第三部，合并一二两部，讲第一步查询出来的结果作为第二部分组完的条件*/

select

dept.department_id,dept.department_name,

count(*),e1.first_name||e1.last_name

from

departments dept,employees e  ,employees e1

where

dept.department_id=e.department_id

and

dept.manager_id=e1.employee_id

group by

dept.department_id,dept.department_name,

e1.first_name||e1.last_name

having count(*)=

(select max(count(*)) from employees

group by department_id )

在这里提醒一下，由于可能出现两个部门人数最多的部门，所以先查出部门人数最多的人数，再去查询部门