今天是实习的第二天,碰到一个问题。
在设计场景时,添加了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+别名。