你想干什么,求最大的部门平均数? select max(avg_sal) max_avg_sal from( SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno);
在一层查询内, 分组函数不可嵌套. select max(sal_avg) from ( SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno )
to Hebo2005: 是的。查部门平均工资中的最大数。to bugchen888: 谢谢。不过我想要结果带上deptno,加上了就会出错。是什么原因呢?
拿来2楼的改一下就行了 select deptno, sal_avg from (SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno) where sal_avg = (select max(sal_avg) from (SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno))
select * from ( SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno order by 2 desc ) where rownum=1
select max(avg_sal) max_avg_sal
from(
SELECT deptno, AVG(sal) avg_sal
FROM
emp
GROUP BY
deptno);
select max(sal_avg)
from
(
SELECT
deptno, AVG(sal) as sal_avg
FROM
emp
GROUP BY
deptno
)
是的。查部门平均工资中的最大数。to bugchen888:
谢谢。不过我想要结果带上deptno,加上了就会出错。是什么原因呢?
select deptno, sal_avg
from (SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno)
where sal_avg =
(select max(sal_avg)
from (SELECT deptno, AVG(sal) as sal_avg FROM emp GROUP BY deptno))
from
(
SELECT
deptno, AVG(sal) as sal_avg
FROM
emp
GROUP BY
deptno
order by 2 desc
)
where rownum=1