P36 流程控制函数

4.流程控制函数

#4.流程控制函数
#if(value,value1,value2) 第一个为真返回value1，为假返回value2

SELECT last_name,salary,IF(salary>=6000,'高工资','低工资')"details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0)"details"
,salary*12*(1+IF(commission_pct IS NOT NULL,commission_pct,0))"annual_sal"
FROM employees;

#ifnull(value1,value2):看做是if(value,value1,value2)的特殊情况,不是null就输出value1
SELECT last_name,commission_pct,IFNULL(commission_pct,0)"details"
FROM employees;

#case when ...then...when...then...else...end
#类似于java的if...else if... else if...else
SELECT last_name,salary,CASE WHEN salary>=15000 THEN '白骨精'
                             WHEN salary>=10000 THEN '潜力股'
                             WHEN salary>=8000 THEN '小屌丝'
                             ELSE '草根' END "details",department_id
FROM employees;

# CASE ...WHEN...THEN...WHEN...THEN...ELSE...END
#类似于java的switch...case
/*
练习1：查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数。
其他部门，打印其工资的 1.4 倍数。
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id 
                                                  WHEN 10 THEN salary*1.1
                                                  WHEN 20 THEN salary*1.2
                                                  WHEN 30 THEN salary*1.3
                                                  ELSE salary * 1.4 END "details"
FROM employees;

/*
练习2：查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数。
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id 
                                                  WHEN 10 THEN salary*1.1
                                                  WHEN 20 THEN salary*1.2
                                                  WHEN 30 THEN salary*1.3
                                                  END "details"
FROM employees
WHERE department_id IN(10,20,30);