--查询出人数最多的那个部门的部门编号和部门名称
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换成任意的数字就可以了,不明白,在线等
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换成任意的数字就可以了,不明白,在线等
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;
如果having中出现的列,则必须出现在group by中或是出现在聚合函数中例:你可以这样写,也是你要的结果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 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;