mysql case when then end 和 if判断 常见使用方法

mysql有两个常用的流程控制运算符：case和if，当条件1时输出结果1，当条件2时输出结果n等待，if是简化的case，只有满足条件和不满足条件2种情况，先看下mysql参考手册说明。

1、case, if参考手册说明

1）case

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE
result] END

语法说明：

• case有两种语法，case value when, case when, 别忘了END结尾；
• 返回值：then匹配到的值或else值，如果没有匹配到也没有else则返回NULL；

-- 'one'
mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; 
-- 'true'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-- NULL
mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

2）IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

mysql> SELECT IF(1>2,2,3); -> 3
mysql> SELECT IF(1<2,'yes','no'); -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'

if可以理解为case when then else的语法糖，要么A要么B。

2、case常见使用场景

1）字段转换输出，比如10表示合同工，20表示临时工，如下：

select emp_no, emp_name, 
	case emp_type when 10 then '合同工' else '临时工' end emp_type_name  
from hr_employee 

2）切换取数来源

饮料企业在进行价格维护时，维护的可以是全国价格，可以是市场价格、地区价格，甚至客户最终价，数据存储格式为：

产品ID	对象类型(obj_type)	对象ID(obj_id)	价格
产品1	全国	0	10
产品1	市场	浙江市场	12
产品1	地区	湖州	11
产品1	客户	客户1	15

先要列出产品价格清单：产品代码、名称、对象类型、对象名称、价格，为此可以使用case根据obj_type进行取数来源切换，从而得到不同类型的obj_name，如下：

select t0.sid, t0.goods_id, t1.goods_no, t1.goods_name, t0.price, 
t0.obj_type, t0.obj_id, 
case t0.obj_type when '客户' then t2.client_no 
	when '市场' then t3.market_name 
	when '地区' then t4.market_name 
	else '全国' end obj_name,
t0.s_date, t0.e_date, t0.status, t0.memo 
from sale_goods_price t0 
inner join base_goods t1 on t0.goods_id = t1.goods_id 
left join sale_client t2 on t0.obj_id = t2.client_id and t0.obj_type = '客户'
left join sale_market_district t3 on t0.obj_id = t3.market_id and t0.obj_type = '市场'
left join sale_market_district t4 on t0.obj_id = t4.market_id and t0.obj_type = '地区'
where t0.status = '已审核' and t0.s_date >= '2019-01-01'

3）where动态条件

下述sql为saas环境汇总租户库存-未发订单得到可用库存的存储过程代码片段，当租户ID(co_id)参数为空时，则取所租户库存余额，当租户ID不为空时则取指定租户的库存余额，sql如下：

	INSERT INTO tmp_stock_20190409 ( co_id, goods_id, stock_count )
	SELECT a.co_id, a.goods_id, a.balance_count
	from wm_inventory a
	where a.balance_count <> 0
	and case in_coid when '' then 1 = 1 else a.co_id = in_coid end
	GROUP BY a.co_id, a.goods_id;

说明：请留意where条件的 1 = 1语法糖

3、IF常见使用场景

case可以使用的简单场景，if都可以使用。

1）字段转换输出

select emp_no, emp_name, if(emp_type=10,'合同工','临时工') emp_type_name
from hr_employee 

2）where动态条件

	INSERT INTO tmp_stock_20190409 ( co_id, goods_id, stock_count )
	SELECT a.co_id, a.goods_id, a.balance_count
	from wm_inventory a
	where a.balance_count <> 0
	and if(in_coid='',1,a.co_id = in_coid)
	GROUP BY a.co_id, a.goods_id;