MySQL 函数 - case when then else end

 

 

 

一、case 【列名称】 when【条件】 then 【返回值】... else 【返回值】end

1、简单单一条件

SELECT
	CASE o.operate_status
	　　WHEN '1' THEN '暂停'
	　　WHEN '2' THEN '启用'
	　　WHEN '3' THEN '失效'
	　　ELSE '其他' END AS operate_status,
	o.change_reason,
	o.creator_name,
	DATE_FORMAT(o.operate_time, '%Y-%m-%d %H:%i') AS operate_time
FROM [tablName] AS o
WHERE o.operate_status in ('1', '2', '3', '4', '5')　　

查询效果：

 

 

 

2、多个条件

SELECT
	CASE o.operate_status
	　　WHEN '1' OR '2' THEN '编制'
	　　WHEN '3' OR '4' THEN '编制'
	　　WHEN '5' THEN '暂停'
	　　WHEN '6' THEN '启用'
	　　WHEN '7' THEN '失效'
	　　ELSE '其他' END AS operate_status,
	CASE o.operate_status
	　　WHEN '1' THEN '暂存信息'
	　　WHEN '2' THEN '提交'
	　　WHEN '3' THEN '审核通过'
	　　WHEN '4' THEN '审核不通过'
	　　WHEN '5' THEN '暂停'
	　　WHEN '6' THEN '启用'
	　　WHEN '7' THEN '失效'
	　　ELSE '其他' END AS operate_result,
	o.change_reason,
	o.creator_name,
	DATE_FORMAT(o.operate_time, '%Y-%m-%d %H:%i') AS operate_time
FROM [tableName] AS o
WHERE o.operate_status in ('1', '2', '3', '4', '5')

查询效果如下：　

 

 

二、case [ when 【条件】then【返回值】... else 【返回值】end ]

SELECT 
	CASE 
	　　WHEN o.operate_status = '1' OR o.operate_status = '2' THEN '编制'
	　　WHEN o.operate_status = '3' OR o.operate_status = '4' THEN '编制'
	　　WHEN o.operate_status = '5' THEN '暂停'
	　　WHEN o.operate_status = '6' THEN '启用'
	　　WHEN o.operate_status = '7' THEN '失效'
	　　ELSE '其他' END AS operate_status,
	o.change_reason,
	o.creator_name,
	DATE_FORMAT(o.operate_time, '%Y-%m-%d %H:%i') AS operate_time
FROM [tableName] AS o
WHERE o.operate_status in ('1', '2', '3', '4', '5');

查询效果如下：