使用SQL编辑基于职务id的薪资

Question

表:雇员

栏：

EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID

来自Employees表的当前输出如下所示：

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        205 Higgins                   AC_MGR          12000
        149 Zlotkey                   SA_MAN          12500
        201 Hartstein                 MK_MAN          13000
        102 De Haan                   AD_VP           17000
        101 Kochhar                   AD_VP           18000

我的SQL：

SELECT employee_id,
last_name,
job_id,
salary
from employees
where (job_id like '%VP' OR
Job_id like '%MAN' OR
Job_id like '%MGR')
AND (salary < 6000 OR salary >11000)
order by 4

我需要一种方法来显示加薪前后的薪水，但我也需要能够为不同的job_ids提供不同的加薪。例如，只给副总裁加薪，而不给经理加薪，或者给副总裁的加薪幅度大于经理。

Answer 1

一种选择是创建一个CTE，它说明谁得到了什么；然后(外部)将它加入到原始表中。就像这样：

SQL> with raise (job, raise_pct) as
  2    (select 'CLERK'  , 10 from dual union all
  3     select 'ANALYST', 20 from dual
  4    )
  5  select e.empno, e.ename, e.job, e.sal old_salary,
  6    e.sal * (1 + r.raise_pct/100) new_salary
  7  from emp e left join raise r on r.job = e.job
  8  order by e.job, e.ename;

     EMPNO ENAME      JOB       OLD_SALARY NEW_SALARY
---------- ---------- --------- ---------- ----------
      7902 FORD       ANALYST         3000       3600
      7788 SCOTT      ANALYST         3000       3600
      7876 ADAMS      CLERK           1100       1210
      7900 JAMES      CLERK            950       1045
      7934 MILLER     CLERK           1300       1430
      7369 SMITH      CLERK            800        880
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7566 JONES      MANAGER         2975
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7654 MARTIN     SALESMAN        1250
      7844 TURNER     SALESMAN        1500
      7521 WARD       SALESMAN        1250

14 rows selected.

SQL>