SQL 加薪的SQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3530298/
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
SQL query for salary increase
提问by Anil Soman
I came up with following query which is not so challenging to resolve but still I think there might be better approach for this. Any suggessions?
我想出了以下查询，解决起来并不那么具有挑战性，但我仍然认为可能有更好的方法。有什么建议吗？
I have got following table:
我有下表：
tb_Salary
tb_Salary
EmpID DeptID Salary
---------------------
1 1 20000
2 1 20000
3 2 30000
4 2 800
5 2 200
I want to increase the salary of employees by Rs. 1000 only when the average salary in the department is more than 35000.
我想将员工的工资增加卢比。1000只在部门平均工资超过35000的时候。
Is this possible using single update query?
这可以使用单个更新查询吗？
I did this in following way. But seems that it is not that smart solution.
我按照以下方式做到了这一点。但似乎这不是那么聪明的解决方案。
UPDATE tb_Salary t1
SET t1.Salary=t1.Salary+1000
WHERE 35000 < (select AVG(t2.Salary) from tb_Salary t2 WHERE t2.DeptID=t1.DeptID)
采纳答案by Tertius Geldenhuys
Your query is the way to go.
您的查询是要走的路。
回答by user330737
UPDATE tb_salary
SET tb_salary.salary = tb_salary + 1000
FROM tb_salary
INNER JOIN
(SELECT avg(salary) AS avg_salary,
deptID
FROM tb_salary
GROUP BY tb_salary.deptID
HAVING avg(salary) > 10000) AS salary_increase
ON salary_increase.deptID = tb_salary.deptID;
I don't have time to test this, so it might not working. Usually I avoid multiple "Where" condition because it is not effective.
我没有时间测试这个，所以它可能不起作用。通常我会避免多个“Where”条件，因为它无效。
回答by Bhagyashri Pingle
update employee
set salary=salary+1000
where deptid in (select deptid from employee group by deptid having sum(salary)>35000);
