Cba薪资:使用SQL根据作业id编辑薪资(sql jobs salary)

表:员工

柱:

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

我需要一种方法来显示加薪前后的工资,但我还需要能够为不同的工作ID提供不同的加薪。例如,只给副总裁加薪,而不给经理加薪,或者给副总裁比经理加薪更多。

0

一种选择是创建一个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>

本站系公益性非盈利分享网址,本文来自用户投稿,不代表边看边学立场,如若转载,请注明出处

(561)
Hone star:Bloxorz a-Star搜索
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(11条)