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
COUNT (e.empno) OVER (PARTITION BY NULL) 中 PARTITION BY NULL怎么解释啊 ?
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
?
这个常规的写法应当是count(e.empno) over()吧?