SQL用部门的平均工资更新员工的工资 [英] SQL to update the salary of employees with their department's average salary

问题描述

考虑表employee:

desc employee;

Name                       Null?    Type
-------------------------- -------- ------------
EMPLOYEENO                 NOT NULL NUMBER(4)
ENAME                               VARCHAR2(15)
JOB                                 VARCHAR2(15)
MGR                                 NUMBER(4)
HIREDATE                            DATE
SAL                                 NUMBER
COMM                                NUMBER
DEPTNO                              NUMBER(2)

包含值为deptno，sal的

    DEPTNO      SAL
---------- ----------
        10     2450
        10     5000
        10     1300
        20     2975
        20     3000
        20     1100
        20      800
        20     3000
        30     1250
        30     1500
        30     1600
        30      950
        30     2850
        30     1250

需要用他们的部门的平均工资来更新员工的工资. 即将每位员工的薪水设置为部门平均水平.group by子句是否可以解决任何问题?

Need to update the salary of employees with their department's average salary. i.e set every employee's salary to the departmental average.Is there any solution possible with the group by clause something like

update employee set sal =(select avg(sal) from employee group by deptno) where deptno in(select deptno from employee group by deptno)

那么如何查询相同的内容.

So how to query the same.

推荐答案

尝试一下

UPDATE EMPLOYEE A
SET SAL = (SELECT AVG(SAL) FROM EMPLOYEE B WHERE B.DEPTNO = A.DEPTNO);

这篇关于SQL用部门的平均工资更新员工的工资的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！