--查询出人数最多的那个部门的部门编号和部门名称
select e.deptno, d.dname, count(1)
from (select cout
from (select count(1) cout
from emp e
group by e.deptno
order by 1 desc)
where rownum = 1) t,
emp e,
dept d
where e.deptno = d.deptno
group by e.deptno, d.dname
having count(1) = t.cout
最后一行报错,把t.cout换成任意的数字就可以了,不明白,在线等
------解决方案--------------------
- SQL code
select deptno,dname,cou from (select dept.deptno,min(dept.dname) dname,count(emp.empno) coufrom dept left join emp on dept.deptno=emp.deptnogroup by dept.deptno order by cou desc) twhere rownum=1;