oracle查询结果转换别名,Oracle子查询的返回结果当做表，主查询使用列别名查询的问题...

今天是实习的第二天，碰到一个问题。

在设计场景时，添加了type_salary逻辑字段(员工工资大于本部门平均工资时显示为A，否则显示为B)。

SELECT avgs.department_id,

avgs.avg_sal

FROM (SELECT emp3.department_id "department_id",

AVG(emp3.salary) "avg_sal"

FROM employees emp3

GROUP BY emp3.department_id

ORDER BY AVG(emp3.salary)) avgs;

上面这段代码是将得到每个部门的员工平均工资，并将部门ID取别名department_id、部门平均工资取别名avg_salary。

在使用sql语句中出现了如图

错误提示为：列别名不能识别

在经过一番折腾去掉列别名的双引号后正常运行

SELECT emp3.department_id department_id department_id,

AVG(emp3.salary) avg_salary

FROM employees emp3

GROUP BY emp3.department_id

ORDER BY AVG(emp3.salary);

SELECT avgs.department_id,

avgs.avg_sal

FROM (SELECT emp3.department_id department_id,

AVG(emp3.salary) avg_sal

FROM employees emp3

GROUP BY emp3.department_id

ORDER BY AVG(emp3.salary)) avgs;

总结：在用子查询做为表时，别名不能用 AS +"别名" 也不能用 "别名"，只能在直接加别名或者AS+别名。