SQL 如何确定高于平均工资的工资
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6403810/
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 to determine salaries greater than the average salary
提问by slafik
Assume I have the following table
假设我有下表
id name city salary dept
and I want to select all salaries which are greater than the average salary
我想选择所有高于平均工资的工资
回答by John Zwinck
Try something like this:
尝试这样的事情：
SELECT salary WHERE salary > (SELECT AVG(salary) FROM *)
回答by Praveen Lobo
Assuming it's mysql, only the below two work. (I used a temp table so the names are different from yours)
假设它是 mysql，只有以下两个工作。（我使用了临时表，因此名称与您的不同）
select * from b where ref > (select avg(ref) from b);
select * from b having ref > (select avg(ref) from b);
This doesn't - select * from b having ref > avg(ref);
这不—— select * from b having ref > avg(ref);
Some queries I tried -
我试过的一些查询 -
mysql> select * from b;
+------+------------+------+
| id | d2 | ref |
+------+------------+------+
| 300 | 2010-12-12 | 3 |
| 300 | 2011-12-12 | 2 |
| 300 | 2012-12-12 | 1 |
| 400 | 2011-12-12 | 1 |
+------+------------+------+
4 rows in set (0.00 sec)
mysql> select * from b having ref > avg(ref);
+------+------------+------+
| id | d2 | ref |
+------+------------+------+
| 300 | 2010-12-12 | 3 |
+------+------------+------+
1 row in set (0.00 sec)
mysql> select * from b having ref > (select avg(ref) from b);
+------+------------+------+
| id | d2 | ref |
+------+------------+------+
| 300 | 2010-12-12 | 3 |
| 300 | 2011-12-12 | 2 |
+------+------------+------+
2 rows in set (0.02 sec)
mysql> select * from b where ref > (select avg(ref) from b);
+------+------------+------+
| id | d2 | ref |
+------+------------+------+
| 300 | 2010-12-12 | 3 |
| 300 | 2011-12-12 | 2 |
+------+------------+------+
2 rows in set (0.00 sec)
mysql> select *,avg(ref) from b having ref > avg(ref);
+------+------------+------+----------+
| id | d2 | ref | avg(ref) |
+------+------------+------+----------+
| 300 | 2010-12-12 | 3 | 1.7500 |
+------+------------+------+----------+
1 row in set (0.00 sec)
回答by Andriy M
If windowed aggregate functions are supported:
如果支持窗口聚合函数：
SELECT Salary
FROM (
SELECT
Salary,
AVG(Salary) OVER () AS AvgSalary
FROM atable
) s
WHERE Salary > AvgSalary
回答by Jasbin karki
its really easy just use following short command given below
它真的很容易使用下面给出的简短命令
SELECT *FROM table_name WHERE salary > avg(select salary from table_name)
HOPE YOU GET IT :-)
希望你明白:-)
回答by Neelabh Singh
If the name of table is Employee(id, name, city, salary)
如果表名是Employee（id，name，city，salary）
select salary from Employee where salary > (select ava(salary) from employee)
回答by Amol Baheti
Assuming emp is the name of the table, which has department id as dept_id
假设 emp 是表的名称，其部门 id 为 dept_id
- Query results shows all employees details whose salary is greater than the average salary of that department. (Department Wise)
- 查询结果显示工资大于该部门平均工资的所有员工明细。（部门明智）
(Group by department)
（按部门分组）
select e1.* from emp e1 inner join (select avg(sal) avg_sal,dept_id from emp group by
dept_id) as e2 on e1.dept_id=e2.dept_id and e1.sal>e2.avg_sal
Query results shows all employees details whose salary is greater than average salary.
select * from emp where sal > (select avg(sal) from emp)
查询结果显示所有工资大于平均工资的员工明细。
select * from emp where sal > (select avg(sal) from emp)
回答by Krishna
select empno,e.deptno,sal
from emp e, ( select deptno,avg(sal) avsal
from emp
group by deptno
) a
where e.sal > a.avsal
and e.deptno = a.deptno;
回答by amit
Following shall work for you.
以下将为您工作。
SELECT salary FROM table_name WHERE salary > (SELECT AVG(salary) FROM table_name);
