select deptno, emp
from (select deptno, count(distinct empno) emp
from scott.emp
group by deptno) t3,
(select t1.emp / dept avg
from (select count(distinct empno) emp from scott.emp) t1,
(select count(distinct deptno) dept from scott.dept) t2) t4
where t3.emp > t4.avg;
select depno, cnt
from (select depno, count(empno) as cnt from emp group by depno)
where cnt > (select count(empno) from emp) /
(select count(distinct depno) from emp) --这里的部门数直接从员工表取值的,此处楼主根据需求改动
SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(DEPTNO)>(SELECT COUNT(DEPTNO)/COUNT(DISTINCT DEPTNO) FROM EMP)