17,086
社区成员
发帖
与我相关
我的任务
分享
--这样就会把最多的所有部门显示出来了,不会只显示第一条了,而是第一组
select deptno,dname,cou from(
select deptno,dname,cou,rank() over (order by cou desc) rn from
(select dept.deptno,min(dept.dname) dname,count(emp.empno) cou
from dept left join emp on dept.deptno=emp.deptno
group by dept.deptno order by cou desc) t)
where rn=1;
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) = max(t.cout);
select deptno,dname,cou from
(select dept.deptno,min(dept.dname) dname,count(emp.empno) cou
from dept left join emp on dept.deptno=emp.deptno
group by dept.deptno order by cou desc) t
where rownum=1;