[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
为什么还是报“非单组函数分组”这个错误？
看这个满足你需求不
[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]
max里 套avg什么意思
[color=red]MAX(DECODE(E.DEPTNO,10,AVG(SAL),20,AVG(SAL),30,AVG(SAL),AVG(SAL)))SAL[/color]
报错因为max里嵌套avg，，看不懂为什么要这么写
[code="sql"]SELECT E.DEPTNO, DNAME, (SELECT MAX(DECODE(DEPTNO,10,AVG(SAL),20,AVG(SAL),30,AVG(SAL),AVG(SAL))) SAL FROM EMP GROUP BY DEPTNO)SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+) GROUP BY E.DEPTNO, DNAME, SAL [/code]
你这个写的有点乱，你把需求整理下，把原始表数据列出来，期望得到的数据列出来，重新给你写一个