甲骨文公司平均加薪

Question

指示是

对于某一特定公司，如果薪资低于公司平均水平，则加薪30%；如果薪资高于平均水平，则提高10%。

桌子上也有空位的人。

所以我试着

with avgs(company_name, salary) as
    (select company_name, avg(salary) avgsalary
    from work
    group by company_name)
select 
work.employee_name, work.salary,
case when work.company_name = avgs.company_name and work.salary > avgs.salary then work.salary * 1.3
     when work.company_name = avgs.company_name and work.salary < avgs.salary then work.salary * 1.1
     when work.company_name is null and work.salary is null then 0
     when avgs.company_name is null and avgs.salary is null then 0
     end as sal_up
from work, avgs

我想要的结果是每人一个结果。当我运行这个时，我会得到太多的结果。

哪种情况不对？有什么我没想过的吗？

Answer 1

使用窗口函数：

select t.*,
       (case when salary < avg_salary then salary * 1.3 else salary * 1.1 end) as new_salary
from (select w.*, avg(salary) over (partition by company_name) as avg_salary
      from work w
     ) w;

这假设您需要一个select，因为这是您问题中的示例查询。

Answer 2

您可以使用windows函数和CASE..WHEN，如下所示：

UPDATE YOUR_TABLE TOUT
SET
    SALARY = (
        SELECT T.NEW_SAL
         FROM ( SELECT T.WORKER_NAME,
                       SALARY * CASE
                                WHEN T.SALARY > AVG(SALARY) OVER(PARTITION BY COMPANY_NAME) 
                                THEN 1.1
                                ELSE 1.3
                                END AS NEWSAL
                  FROM YOUR_TABLE T
              ) T
        WHERE TOUT.WORKER_NAME = T.WORKER_NAME)