<div id="cnblogs_post_body" class="blogpost-body">
<p><strong>0、创建一张数据表 表名为 test_when_case</strong></p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>CREATE TABLE `test_when_case` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = 幼儿园 1 = 一年级 2 = 二年级 3 = 三年级 4 = 四年级 5 = 五年级 6 = 六年级 7 = 七年级 8 = 八年级 9 = 九年级',
`state` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = 等待审核 1 = 审核通过 2 = 未通过审核',
`instrDataTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`addDataTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p><strong>1、添加5-10条的数据</strong></p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
<pre>INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (0,0,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (1,1,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (2,2,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (3,0,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (4,1,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (5,2,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (6,0,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (7,1,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (8,2,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (9,0,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (2,1,(select concat_ws(' ',curdate(),curtime(),null)));
INSERT INTO test_when_case (`type`,`state`,`addDataTime`) values (5,2,(select concat_ws(' ',curdate(),curtime(),null)));</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" /></a></span></div>
</div>
<p><strong>2、用case when then end 函数进行查询 实现的效果是 type字段 state字段 的数字转换成中文</strong></p>
<div class="cnblogs_code">
<pre>SELECT
CASE type WHEN '0' THEN '幼儿园' WHEN '1' THEN '一年级' WHEN '2' THEN '二年级' WHEN '3' THEN '三年级' WHEN '4' THEN '四年级' WHEN '5' THEN '五年级' WHEN '6' THEN '六年级' WHEN '7' THEN '七年级' WHEN '8' THEN '八年级' WHEN '9' THEN '九年级' END AS TYPE,
CASE state WHEN '0' THEN '待审核' WHEN '1' THEN '审核通过' WHEN '2' THEN '未通过审核' END AS STATE
FROM test_when_case;</pre>
</div>
<p><strong>3、以上查询语句返回图</strong></p>
<p> <img src="https://images2017.cnblogs.com/blog/1235079/201709/1235079-20170927202401606-972421314.png" alt="" /></p>
<p>文章来源:<a id="Editor_Edit_hlEntryLink" title="view: &lt;小田吃饺子&gt; Mysql：case when then end 的用法" href="http://www.cnblogs.com/hello-tl/p/7603676.html" target="_blank">http://www.cnblogs.com/hello-tl/p/7603676.html</a> </p>
</div>
<div id="MySignature">LINUX 简单的,但只有天才才能知道他的简单</div>
