oracle 查询错误

Question

[code="sql"]
SELECT E.DEPTNO, DNAME,MAX(DECODE(E.DEPTNO,10,AVG(SAL),20,AVG(SAL),30,AVG(SAL),AVG(SAL)))SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+) GROUP BY E.DEPTNO, DNAME, SAL
[/code]

ERROR 位于第 2 行:
ORA-00937: not a single-group group function

为什么还是报“非单组函数分组”这个错误？

Answer 1

看这个满足你需求不
[code="sql"]SELECT m.deptno,
d.dname,
m.sal
FROM (SELECT t.deptno,
AVG(sal) sal,
RANK() over(ORDER BY AVG(sal) DESC) cnt
FROM emp t
GROUP BY t.deptno) m
LEFT JOIN dept d ON m.deptno = d.deptno
WHERE m.cnt = 1[/code]