英文:
How to get the employees who have a salary that is more than 50% of the average salary?
问题
我有两张表 - ```employees``` 和 ```departments```。
```employees``` 表有以下列：```emp_id```，```salary```，```dep_id```。
```departments``` 表有以下列：```dep_id```，```department_name```，```manager```。
现在，我想获取工资超过平均工资50%的员工的```emp_id```，以及部门经理的姓名。
到目前为止，我写了这个查询：
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
然而，我一直得到这个错误：
Uncaught Error: no such column: emp_id
我正在使用以下链接执行我的代码：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:
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
However, I keep getting this error:
Uncaught Error: no such column: emp_id
I am using the following to execute my code: https://www.sqltutorial.org/seeit/
答案1
得分: 0
我编辑了我的答案-这在我的数据集上有效。
英文:
I edited my answer- this works on my data set.
SELECT employee_id, d.department_id
FROM employees
INNER JOIN departments as d
ON employees.department_id = d.department_id
WHERE salary > (SELECT AVG(salary) FROM employees) * 1.5
答案2
得分: 0
您可以更容易地使用子查询拆分逻辑，以找到所有工资高于平均工资的员工：
SELECT
e.employee_id,
e.manager_id,
e.department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary) * 1.5 FROM employees
);
然后找到所有经理的列表：
SELECT
manager_id,
email,
department_id
FROM employees
WHERE manager_id IS NULL
然后，您可以将它们组合以获得最终数据：
SELECT
e.employee_id,
e.manager_id,
e.department_id,
managers.email
FROM employees e
JOIN (
SELECT
manager_id,
email,
department_id
FROM employees
WHERE manager_id IS NULL
) managers ON managers.department_id = e.department_id
WHERE salary > (
SELECT AVG(salary) * 1.5 FROM employees
);
返回以下结果：
e.employee_id e.manager_id e.department_id managers.email
100 null 9 steven.king@sqltutorial.org
101 100 9 steven.king@sqltutorial.org
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:
SELECT
e.employee_id,
e.manager_id,
e.department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary) * 1.5 FROM employees
);
Then to find a list of all managers:
SELECT
manager_id,
email,
department_id
FROM employees
WHERE manager_id IS NULL
Then you can combine these to get the final data:
SELECT
e.employee_id,
e.manager_id,
e.department_id,
managers.email
FROM employees e
JOIN (
SELECT
manager_id,
email,
department_id
FROM employees
WHERE manager_id IS NULL
) managers ON managers.department_id = e.department_id
WHERE salary > (
SELECT AVG(salary) * 1.5 FROM employees
);
Returns the following:
e.employee_id e.manager_id e.department_id managers.email
100 null 9 steven.king@sqltutorial.org
101 100 9 steven.king@sqltutorial.org
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论