#if(expr1,expr2,expr3) 表达式expr1成立(为真),则结果为expr2,否则为expr3
SELECT IF(`name`='user1', 'user1','user2') AS `result` FROM `a` WHERE `id` = 1;
#返回值 user1
SELECT IF(`name`='user1', 'user1','user2') AS `result` FROM `a` WHERE `id` = 2;
#返回user2
#嵌套
SELECT IF(`name`='user3','user3',IF(`name`='user1', 'user1','user2')) AS `result` FROM `a` WHERE `id` = 3;
#返回user3
#CASE表示函数开始,END表示函数结束。当有一个成立之后,后面的就不执行了。
SELECT CASE WHEN `name` = 'user1' THEN 'user1' WHEN `name` = 'user2' THEN 'user2' WHEN `name` = 'user3' THEN 'user3' ELSE 'unknow' END AS `rs` FROM `a` WHERE `id` = 3;
#简单写法 类似php的switch
SELECT CASE `name` WHEN 'user1' THEN 'user1' WHEN 'user2' THEN 'user2' WHEN 'user3' THEN 'user3' ELSE 'unknow' END AS `rs` FROM `a` WHERE `id` = 1;
需要注意的是,如果where条件指定的字段没有这条数据的时候,case不执行,不会返回unknown。
原创文章,转载请注明。本文链接地址: https://www.rootop.org/pages/3567.html