【练习】条件表达式

1.case

SQL> SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*  2  salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;
  3    4    5    6  
LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell                  SH_CLERK         2600           2600
Grant                     SH_CLERK         2600           2600
Whalen                    AD_ASST          4400           4400
Hartstein                 MK_MAN          13000          13000
Fay                       MK_REP           6000           6000
Mavris                    HR_REP           6500           6500
Baer                      PR_REP          10000          10000
Higgins                   AC_MGR          12008          12008
Gietz                     AC_ACCOUNT       8300           8300
King                      AD_PRES         24000          24000
Kochhar                   AD_VP           17000          17000

2.decode

SQL> SELECT last_name, salary,
       DECODE (TRUNC(salary/2000, 0),
                  2    3           0, 0.00,
                         1, 0.09,
                         2, 0.20,
                         3, 0.30,
                         4, 0.40,
                         5, 0.42,
                         6, 0.44,
                            0.45) TAX_RATE
FROM   employees
WHERE  department_id = 80;
  4    5    6    7    8    9   10   11   12  
LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
Russell                        14000        .45
Partners                       13500        .44
Errazuriz                      12000        .44
Cambrault                      11000        .42
Zlotkey                        10500        .42
Tucker                         10000        .42
Bernstein                       9500         .4
Hall                            9000         .4
Olsen                           8000         .4
Cambrault                       7500         .3
Tuvault                         7000         .3

 

转载于:https://www.cnblogs.com/tomatoes-/p/6066088.html