日期:2014-05-17 浏览次数:20789 次
SELECT DISTINCT a.deptno,dept.dname FROM (SELECT deptno,COUNT(empno) AS count_dpem FROM emp GROUP BY deptno) a INNER JOIN (SELECT emp.deptno,e.count_empno/d.count_deptno AS avg_empno FROM (SELECT COUNT(deptno) AS count_deptno FROM dept) d, (SELECT COUNT(empno) AS count_empno FROM emp) e,emp) b ON a.deptno=b.deptno INNER JOIN dept ON a.deptno=dept.deptno WHERE a.count_dpem>b.avg_empno;
SELECT deptno, dname FROM (SELECT e.deptno, d.dname, COUNT (e.empno) OVER (PARTITION BY e.deptno) dcount, COUNT (e.empno) OVER (PARTITION BY NULL) ecount, COUNT (DISTINCT (d.deptno)) OVER (PARTITION BY NULL) deptcount FROM emp e, dept d WHERE e.deptno = d.deptno) WHERE dcount > (ecount / deptcount) GROUP BY deptno, dname