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');
查询效果如下:
您的资助是我最大的动力!
金额随意,欢迎来赏!
如果,您希望更容易地发现我的新博客,不妨点击一下绿色通道的
欢迎大家关注我的个人博客 Lycos | 小站 !由于最近没时间处理,原域名http://www.liuyuchuan.com暂时停止使用