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

1、题目：显示员工数最多的部门信息，显示部门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、<1>/*第一步，查询出员工人数最多的部门的员工人数*/

     select max(count(*)) from employees 

group by department_id

2、<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、<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 )

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