编写查询以显示具有最大学生数量的系名。

Question

这是模式编写一个查询以显示具有最大学生数量的部门的名称。

这就是我们要尝试的。

select d.department_name,count(s.student_id)
     from department d left join student s
     on d.department_id=s.department_id 
     group by d.department_name,d.department_id
     order by d.department_name;

我觉得我的代码里缺少了什么

Answer 1

你快到了。

将结果按学生人数降序排列，然后取第一行：

SELECT department_name
FROM
(
  SELECT   d.department_name,
           COUNT(*) AS nr_students
  FROM     department d
  JOIN     student    s
    ON     d.department_id = s.department_id
  GROUP BY d.department_name
  ORDER BY nr_students DESC
)
WHERE ROWNUM <= 1;

Answer 2

根据上面提到的架构，您必须从staff表向部门表进行连接(内部连接)，才能获得部门的名称。

如果不需要部门的名称，并且可以只基于department_id进行计数，则不需要连接。

下面将介绍对这两种场景的查询。

Oracle查询具有部门名称的结果，即内部联接

SELECT D.DEPARTMENT_NAME, COUNT(S.DEPARTMENT_ID) AS STAFF_COUNT FROM **DEPARTMENT D, STAFF S** --INDICATES INNER JOIN IN ORACLE SQL
WHERE D.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
ORDER BY STAFF_COUNT DESC

Oracle查询没有部门名称的结果，只查询department_id

SELECT S.DEPARTMENT_ID,COUNT(S.DEPARTMENT_ID) AS STAFF_COUNT FROM STAFF S
GROUP BY S.DEPARTMENT_ID
ORDER BY STAFF_COUNT DESC

希望这能有所帮助。干杯。

Answer 3

我试过了而且成功了。

​

select department_name 
from department d inner join student s 
on s.department_id=d.department_id 
having count(*) in (
    select max(count(student_id)) 
    from student s join department d 
    on d.department_id=s.department_id 
    group by d.department_id)
group by d.department_id,department_name;

​