oracle嵌套函数的使用方法,sql聚合函数嵌套问题

sql聚合函数嵌套问题

我现在要查的是top client(买东西总和加起来最多的)

所以我输了 SELECT C.CLIENTNO, C.CNAME, MAX(SUM(P.AMOUNT))

FROM CLIENT C,PURCHASE P

WHERE C.CLIENTNO=P.CLIENTNO

GROUP BY C.CLIENTNO,C.CNAME;

提示说不是单组分组函数，所以 问题应该是出在 max(sum(p.amount))上吧，应该如何分开来写啊

with t as

(SELECT job, deptno, sum(sal) sal FROM emp group by job, deptno)

select * from t where sal = (select max(sal) from t)

SELECT  CLIENTNO,CNAME,MAX_VALUE

FROM

(SELECT C.CLIENTNO, C.CNAME, SUM(P.AMOUNT) MAX_VALUE

FROM CLIENT C,PURCHASE P

WHERE C.CLIENTNO=P.CLIENTNO

GROUP BY C.CLIENTNO,C.CNAME) TT1

WHERE TT1.MAX_VALUE

IN

(SELECT  MAX(MAX_VALUE)

FROM

(SELECT C.CLIENTNO, C.CNAME, SUM(P.AMOUNT) MAX_VALUE

FROM CLIENT C,PURCHASE P

WHERE C.CLIENTNO=P.CLIENTNO

GROUP BY C.CLIENTNO,C.CNAME) TT);