Mysql：case when then end 的用法 · 我的隨筆

<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>