我试图找到公司员工的薪水高于所有员工的平均薪水。我想从一开始就指出,我不希望平均薪水出现在最终结果中,因此我在SELECT语句中忽略了它。这些是我尝试过的事情:
SELECT employee.lastname,employee.firstname,employee.salary FROM employee
GROUP BY employee.salary
HAVING employee.salary > avg(employee.salary);
这将导致结果表为空
但是,尽管有“ =”符号,但以下内容令人惊讶地返回了公司的所有员工。
SELECT employee.lastname,employee.firstname,employee.salary FROM employee
GROUP BY employee.salary
HAVING employee.salary = avg(employee.salary);
这将再次返回空表:
SELECT employee.lastname,employee.firstname,employee.salary FROM employee
WHERE (SELECT avg(employee.salary) FROM employee
GROUP BY employee.salary
HAVING employee.salary > AVG(employee.salary));
因此,总结一下这篇文章,我将对有关正确使用HAVING和聚合函数的一些见解,以及有关摘要导致空表的原因的见解。
当你GROUP BY employee.salary
那么每个组的平均工资等于employee.salary
,因为该组的所有工资都是平等的。
因此条件:
employee.salary > avg(employee.salary)
总是,FALSE
并且您没有行,并且条件:
employee.salary = avg(employee.salary)
始终存在TRUE
,结果是返回所有组。
获得所需内容的正确代码是:
SELECT employee.lastname, employee.firstname, employee.salary
FROM employee
WHERE employee.salary > (SELECT avg(employee.salary) FROM employee);
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句