select avg_sal, deptno from (select avg(sal) avg_sal , deptno from emp group by deptno)t where
avg_sal = (select max(avg_sal) from t);avg_sal = (select max(avg_sal) from t)
ORA-00942: 表或视图不存在
为什么呢。。
(select avg(sal) avg_sal , deptno from emp group by deptno)t
不是已经定义好t了么
avg_sal = (select max(avg_sal) from t);avg_sal = (select max(avg_sal) from t)
ORA-00942: 表或视图不存在
为什么呢。。
(select avg(sal) avg_sal , deptno from emp group by deptno)t
不是已经定义好t了么
from (
select avg(sal) avg_sal , deptno
from emp
group by deptno
) t
where avg_sal = (select max(avg_sal) from t);这个 from 它只会去表中或者视图中找。 语法机制上的原因。
from
(select avg(sal) avg_sal , deptno
from emp
group by deptno) t
where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal , deptno
from emp
group by deptno) t );
with t as
(SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno)
SELECT avg_sal, deptno
FROM t
WHERE avg_sal = (SELECT MAX(avg_sal) FROM t);--2、你也可以这样写
SELECT avg_sal, deptno
FROM (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) t
WHERE avg_sal =
(SELECT MAX(avg_sal) FROM (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno));