获取薪水高于平均薪水50%的员工。

huangapple
go评论97阅读模式
英文:

How to get the employees who have a salary that is more than 50% of the average salary?

问题

  1. 我有两张表 - ```employees``` 和 ```departments```。
  3. ```employees``` 表有以下列：```emp_id```，```salary```，```dep_id```。
  5. ```departments``` 表有以下列：```dep_id```，```department_name```，```manager```。
  7. 现在，我想获取工资超过平均工资50%的员工的```emp_id```，以及部门经理的姓名。
  9. 到目前为止，我写了这个查询：

SELECT emp_id, manager
FROM
(SELECT (salary / AVG(salary) * 100) AS percentage
FROM employees )
INNER JOIN departments
ON employees.dep_id = departments.dep_id
WHERE percentage > 50

  2. 然而，我一直得到这个错误：

Uncaught Error: no such column: emp_id

  2. 我正在使用以下链接执行我的代码：https://www.sqltutorial.org/seeit/
英文:

I have two tables - employees and departments.

The employees table has the following columns: emp_id, salary, dep_id.

The departments table has the following: dep_id, department_name, manager.

Now, I am trying to get the employee_ids of the employees who have a salary that is more than 50% of the average salary as well as the name of the department manager.

I have written this so far:

  1. SELECT emp_id, manager
  2. FROM
  3.   (SELECT (salary / AVG(salary) * 100) AS percentage
  4.   FROM employees )
  5. INNER JOIN departments
  6.    ON employees.dep_id = departments.dep_id
  7. WHERE percentage > 50

However, I keep getting this error:

  1. Uncaught Error: no such column: emp_id

I am using the following to execute my code: https://www.sqltutorial.org/seeit/

答案1

得分: 0

  1. 我编辑了我的答案-这在我的数据集上有效。
英文:

I edited my answer- this works on my data set.

  1. SELECT employee_id, d.department_id
  2. FROM employees
  3. INNER JOIN departments as d
  4.    ON employees.department_id = d.department_id
  5.    
  6. WHERE salary > (SELECT AVG(salary) FROM employees) * 1.5

答案2

得分: 0

您可以更容易地使用子查询拆分逻辑，以找到所有工资高于平均工资的员工：

  1. SELECT
  2.     e.employee_id,
  3.     e.manager_id,
  4.     e.department_id
  5. FROM employees e
  6. WHERE salary > (
  7.     SELECT AVG(salary) * 1.5 FROM employees
  8. );

然后找到所有经理的列表：

  1. SELECT
  2.     manager_id,
  3.     email,
  4.     department_id
  5. FROM employees
  6. WHERE manager_id IS NULL

然后，您可以将它们组合以获得最终数据：

  1. SELECT
  2.     e.employee_id,
  3.     e.manager_id,
  4.     e.department_id,
  5.     managers.email
  6. FROM employees e
  7. JOIN (
  8.   SELECT
  9.     manager_id,
  10.     email,
  11.     department_id
  12.   FROM employees
  13.   WHERE manager_id IS NULL
  14. ) managers ON managers.department_id = e.department_id
  15. WHERE salary > (
  16.     SELECT AVG(salary) * 1.5 FROM employees
  17. );

返回以下结果：

  1. e.employee_id    e.manager_id    e.department_id    managers.email
  2. 100              null            9                 steven.king@sqltutorial.org
  3. 101              100             9                 steven.king@sqltutorial.org
  4. 102              100             9                 steven.king@sqltutorial.org

如果您需要额外的经理数据（名字/姓氏），您可以编辑managers子查询以从employees表中选择相关列，并在主SELECT中包括这些新列。

英文:

You can split the logic more easily using subqueries. So to find all employees that have a higher-than-average salary:

  1. SELECT
  2. 	e.employee_id,
  3. 	e.manager_id,
  4. 	e.department_id
  5. FROM employees e
  6. WHERE salary > (
  7. 	SELECT AVG(salary) * 1.5 FROM employees
  8. );

Then to find a list of all managers:

  1. SELECT
  2. 	manager_id,
  3. 	email,
  4. 	department_id
  5. FROM employees
  6. WHERE manager_id IS NULL

Then you can combine these to get the final data:

  1. SELECT
  2. 	e.employee_id,
  3. 	e.manager_id,
  4. 	e.department_id,
  5. 	managers.email
  6. FROM employees e
  7. JOIN (
  8.   SELECT
  9.   	manager_id,
  10.   	email,
  11.   	department_id
  12.   FROM employees
  13.   WHERE manager_id IS NULL
  14. ) managers ON managers.department_id = e.department_id
  15. WHERE salary > (
  16. 	SELECT AVG(salary) * 1.5 FROM employees
  17. );

Returns the following:

  1. e.employee_id	e.manager_id	e.department_id	managers.email
  2. 100	            null	        9	            steven.king@sqltutorial.org
  3. 101	            100	            9	            steven.king@sqltutorial.org
  4. 102         	100	            9	            steven.king@sqltutorial.org

If you need additional manager data (first/last name), you can edit the managers subquery to select the relevant columns from the employees table and include those new columns in the main SELECT.

huangapple
  • 本文由 发表于 2023年3月9日 22:22:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/75685867.html
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定