IF(value,value1,value2)
如果value的值为TRUE,返回value1,否则返回value2
案例:如果薪资>=6000成立,就返回 高工资 否则返回 低工资
if(salary>=6000,"高工资","低工资")
案例:如果commission_pct不为null值,就返回 自身,否则就返回 0
if(commission_pct is not null,commission_pct,0)
IF NULL(value1, value2)
如果value1不为NULL,返回value1,否则返回value2
案例:如果commission_pct不为null值,就返回 自身,否则就返回 0
ifNull(commission_pct,0)
case when 条件1 then 结果1 when 条件2 then 结果2… [else 最后结果] end
相当于Java的if…else if…else…
SELECT employee_id,salary, CASE
WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END as "描述"
FROM employees;
case expr when 常量值1 then 值1 when 常量值1 then 值1 … [else 值n] end
相当于Java的switch…case…
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END as "REVISED_SALARY"
FROM employees;