SELECT deptno FROM a WHERE a.avg_sal=(SELECT MAX(a.avg_sal) FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a);
(SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a,我需要两次用到子查询的别名a,但是无论是前面使用别名还是后面使用别名都会报错,说表a不存在。只有两处都写全才能生效,为什么啊?也太不简化了吧?
...全文
4794打赏收藏
一个查询语句多次引用子查询别名报错!
SELECT deptno FROM a WHERE a.avg_sal=(SELECT MAX(a.avg_sal) FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a); (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a,我需要两次用到子查询的别名a,但是无论是前面使用别名还是后面使用别名都会报错,说表a不存在。只有两处都写全才能生效,为什么啊?也太不简
SELECT deptno FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) as a WHERE a.avg_sal=(SELECT AVG(sal) FROM emp GROUP BY deptno order by AVG(sal) desc limit 1);
两处是指:
1:红色的a,这个a要换成(SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a
2:最后的(SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a
也就是写成
SELECT deptno FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a WHERE a.avg_sal=(SELECT MAX(a.avg_sal) FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno)a);
这样才有效。只要不写成这样,无论哪里使用别名a都会报错。