表：员工
柱：
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>
本站系公益性非盈利分享网址，本文来自用户投稿，不代表边看边学立场，如若转载，请注明出处
评论列表（5条）