日期:2014-05-16 浏览次数:20418 次
现在有需求,计算出每个雇员的收入,收入=工资+福利。
create table plch_employees ( employee_id number (4) NOT NULL , employee_name varchar2 (10) NOT NULL , employee_salary number (7,2) NOT NULL , employee_comm number (7,2) ); insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm) values ( 7369, 'SMITH' , 800 , NULL); insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm) values ( 7499, 'ALLEN' , 1600, 300 ); insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm) values ( 7521, 'WARD' , 1250, 500 ); insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm) values ( 7566, 'JONES' , 2975, NULL);这是个非常简单的需求,随手写了一个,发现有问题。
SQL> select e.employee_name,
e.employee_salary,
e.employee_comm,
e.employee_comm + e.employee_salary as total
from plch_employees e;
EMPLOYEE_N EMPLOYEE_SALARY EMPLOYEE_COMM TOTAL
---------- --------------- ------------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
这种场景,可以使用coalesce 。
SQL> select e.employee_name,
e.employee_salary,
e.employee_comm,
coalesce(e.employee_comm + e.employee_salary, e.employee_salary) as total
from plch_employees e;
EMPLOYEE_N EMPLOYEE_SALARY EMPLOYEE_COMM TOTAL
---------- --------------- ------------- ----------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
coalesce 解释: