关于为什么以下内容不起作用的任何想法。该表名为"收入",共有三列:姓名、部门和工资。我想知道市场部员工的名字和工资,他们的工资低于所有员工的平均工资。当我运行以下程序时,我得到错误1111。
SELECT name, salary
FROM income
WHERE dept = "marketing"
HAVING salary < AVG(salary)
必须在WHERE子句中使用一个子查询,该子查询返回平均值:
SELECT name, salary
FROM income
WHERE dept = 'marketing'
AND salary < (SELECT AVG(salary) FROM income WHERE dept = 'marketing')
如果所有员工是指所有部门员工的平均工资,则从子查询中删除WHERE dept = 'marketing'
。
作为将子查询放入WHERE子句的替代方案,我们可以使用内联视图:
SELECT t.name
, t.salary
FROM ( SELECT AVG(d.salary) AS avg_salary
FROM income d
WHERE d.dept = 'marketing'
) a
JOIN income t
ON t.salary > a.avg_salary
AND t.dept = 'marketing'
使用内联视图,我们还可以返回平均工资,并且我们可以计算多个部门的差异,甚至百分比差异
对查询进行一点扩展,如下所示:
SELECT a.dept
, t.name
, t.salary
, a.avg_salary
, ((t.salary - a.avg_salary) / a.avg_salary) * 100.0 AS pct_greater
FROM ( SELECT d.dept
, AVG(d.salary) AS avg_salary
FROM income d
GROUP
BY d.dept
) a
JOIN income t
AND t.dept = a.dept
ON t.salary > a.avg_salary
ORDER
BY a.dept
, t.salary DESC