SQL 我怎样才能得到工资低于平均工资的所有员工？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13808746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get all employees with a salary less than the average salary?
提问by brian
I can get the count of employees and avg salary but when I try to get the the addition select of listing the number of employees paid below the average it fails.
我可以获得员工人数和平均工资，但是当我尝试获得列出低于平均工资的员工人数的附加选择时，它失败了。
select count(employee_id),avg(salary)
from employees
Where salary < avg(salary);
回答by
select count(*), (select avg(salary) from employees)
from employees
where salary < (select avg(salary) from employees);
回答by Gordon Linoff
The problem is that AVGis an aggregation function. SQL is not smart enough to figure out how to mix aggregated results within the rows. The traditional way is to use a join:
问题是这AVG是一个聚合函数。SQL 不够聪明，无法弄清楚如何在行中混合聚合结果。传统的方法是使用连接：
select count(*), avg(e.salary),
sum(case when e.salary < const.AvgSalary then 1 else 0 end) as NumBelowAverage
from employees e cross join
(select avg(salary) as AvgSalary from employees) as const
回答by Jonathan Leffler
It isn't clear which columns you want in your result set, which makes it difficult to answer your question. Making the question clear improves the quality of the answers.
不清楚您想要结果集中的哪些列，这使得回答您的问题变得困难。明确问题可以提高答案的质量。
You seem to want 3 facts:
你似乎想要 3 个事实：
- Number of employees.
- Average salary.
- Number of employees earning less than the average salary.
- 在职员工人数。
- 平均工资。
- 收入低于平均工资的员工人数。
And you show a query which does the job for the first two facts:
并且您显示了一个查询，该查询可以完成前两个事实的工作：
SELECT COUNT(*) AS NumberOfEmployees,
AVG(Salary) AS AverageSalary
FROM Employees
What's the difference between COUNT(*)and COUNT(Employee_ID)? The difference is that the latter only counts the rows where there is a non-NULL value in the Employee_ID column. A good optimizer will recognize that Employee_ID is a primary key and contains no NULL values, and the query will be the same. But COUNT(*)is more conventional and less reliant on the optimizer.
COUNT(*)和 和有COUNT(Employee_ID)什么区别？区别在于后者只计算Employee_ID 列中存在非NULL 值的行。一个好的优化器会识别出 Employee_ID 是一个主键并且不包含 NULL 值，并且查询将是相同的。但COUNT(*)更传统，对优化器的依赖更少。
The other statistic can be generated as a simple value in the select-list via a sub-query:
另一个统计信息可以通过子查询作为选择列表中的一个简单值生成：
SELECT COUNT(*) AS NumberOfEmployees,
AVG(Salary) AS AverageSalary,
(SELECT COUNT(*)
FROM Employees
WHERE Salary < (SELECT AVG(Salary) FROM Employees)
) AS NumberOfEmployeesPaidSubAverageWages
FROM Employees
Under many circumstances, it would not be appropriate to write the sub-query like that, but for the interpretation of the specified query, it is fine.
在很多情况下，这样写子查询是不合适的，但是对于指定查询的解释，是可以的。
回答by RichardTheKiwi
select TotalNumberOfEmployees,
AverageSalary,
count(e.employee_id) NumberOfEmployeesBelowAverageSalary
from (
select count(employee_id) TotalNumberOfEmployees,
avg(salary) AverageSalary
from employees
) preagg
left join employees e on e.salary < preagg.AverageSalary
group by TotalNumberOfEmployees,
AverageSalary
Note: I used a LEFT join so if you had 3 equal employees, it would show 0 instead of no results (nobody below below average).
注意：我使用了 LEFT 连接，因此如果您有 3 个相同的员工，它将显示 0 而不是没有结果（没有人低于平均水平）。
回答by Kishore
select * from <table name> where salary < (select avg(<salary column name) from <table name>);
Example:
例子：
select * from EMPLOYEE where sal < (select avg(emp_sal) from EMPLOYEE);
回答by user9269233
SELECT e.ename,e.deptno,e.sal,d.avg
FROM emp e,(SELECT deptno, avg(sal) avg
FROM emp
GROUP BY deptno) d
WHERE e.deptno=d.deptno
AND
e.sal < d.avg
