case when then else_MySQL基础之case使用

为了方便测试，先创建一张员工表，包含员工姓名、工作、工资和部门编号的信息。

 CREATE TABLE `emp` ( `id` int(255) NOT NULL AUTO_INCREMENT, `ename` varchar(255) DEFAULT NULL, `job` varchar(255) DEFAULT NULL, `sal` int(11) DEFAULT NULL, `deptNo` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

插入几行数据用于测试

case使用一：类似java中的switch的效果

java中的switch：switch (变量/表达式) { case 常量1:语句1； break; case 常量1:语句1； break; …… default:语句n; break;}

在mysql中格式：

case 要判断的字段/表达式when 常量1 then 要显示的值1或语句1;(如果是语句要加分号)when 常量2 then 要显示的值2或语句2;……else 要显示的值n或语句n;end

then后面如果是常量值不需要分号，如果是语句则要加分号。

实例：

部门编号是1的，显示工资为0.1

部门编号是2的，显示工资为0.2

部门编号是3的，显示工资为0.3

显示名称，部门编号，原工资，新工资

mysql>select ename,deptNo,sal '原工资', case deptNo when 1 then sal*0.1 when 2 then sal*0.2 when 3 then sal*0.3 end as '新工资' from emp;+--------+--------+--------+--------+| ename | deptNo | 原工资 | 新工资 |+--------+--------+--------+--------+| Emma | 1 | 5000 | 500.0 || Rose | 2 | 3500 | 700.0 || Carl | 1 | 3000 | 300.0 || Anette | 3 | 4000 | 1200.0 || Zara | 3 | 2500 | 750.0 |+--------+--------+--------+--------+

case使用二：类似于java中的多重if的效果

java中多重ifif(条件1){ 语句1;  }else if(条件2) { 语句2;  }……else{ 语句n;}

在mysql中：

case (case后什么都没有，没有需要判断的东西)when 条件1 then 要显示的值1或语句1;when 条件2 then 要显示的值2或语句2;……else 要显示的值n或语句nend

实例：查询员工的工资情况

如果工资大于4000，显示A级别

如果工资大于3000，显示B级别

如果工资大于2000，显示C级别

其他D级别

显示名称，工资，级别

select ename,sal, case  when sal > 4000 then 'A' when sal > 3000 then 'B' when sal > 2000 then 'C' else 'D' end as '工资级别'from emp +--------+------+----------+| ename | sal | 工资级别 |+--------+------+----------+| Emma | 5000 | A || Rose | 3500 | B || Carl | 3000 | C || Anette | 4000 | B || Zara | 2500 | C |+--------+------+----------+

实例：使用case解决排序空值

emp表加一个字段orderNo用于排序，但是这个字段允许为空值。现在要求查询所有员工信息。查询结果中，orderNo是空值的数据要一直排在最后面。

加字段： alter table emp add orderNo varchar(20) default null;

处理一下数据

用case来标记一个一个值是否为空值，然后在order by子句中增加标记列，便可以很容易地控制空值列在前面还是在最后。

select ename sal,orderNo from ( select ename, sal,orderNo, case  when orderNo is null then 0 else 1 end as isNull from emp )x order by isNull desc,orderNo+--------+---------+| sal | orderNo |+--------+---------+| Rose | 0 || Carl | 1 || Emma | 2 || Anette | NULL || Zara | NULL |+--------+---------+

x就像一个视图，有ename,，sal，orderNo和标记空值的列isNull。

order by isNull desc 语句将orderNo为空值的列排在结果的最后，若想把它们放在前面，只需要在语句让其升序排列：order by isNull 。

select ename sal,orderNo from ( select ename, sal,orderNo, case  when orderNo is null then 0 else 1 end as isNull from emp )x order by isNull,orderNo+--------+---------+| sal | orderNo |+--------+---------+| Anette | NULL || Zara | NULL || Rose | 0 || Carl | 1 || Emma | 2 |+--------+---------+

好，关于case的整理到此，记住它的两个使用方式。