177.case...when...与 if....else

最新推荐文章于 2024-04-30 14:56:16 发布

weixin_34191734

最新推荐文章于 2024-04-30 14:56:16 发布

阅读量270

点赞数

原文链接：https://my.oschina.net/springMVCAndspring/blog/1925196

版权

2019独角兽企业重金招聘Python工程师标准>>>

1. case....when

Case具有两种格式：

                            --简单Case函数
CASE sex
      WHEN '1' THEN '男'
     WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

1.1 语法一：（针对结果有两种的）

            CASE WHEN 条件判断
               THEN '成立'
              ELSE '不成立
              END AS 别名

效果：

SELECT t1.username,t1.hobby ,t1.workage,t1.phonenumber,t1.personcardnumber,t1.address,t1.createtime,t1.departmentname,t1.jobname,

CASE WHEN t1.status='1'
THEN '在职'
ELSE '离职'
END AS STATUS

,t1.isadmin,t1.other FROM t_user t1 ORDER BY STATUS DESC

1.2 多条件判断

SELECT t1.username,
   CASE t1.hobby
       WHEN '1' THEN '散步'
       WHEN '2' THEN '爬山'
       WHEN '3' THEN '读书'
       WHEN '1,2' THEN '散步,爬山'
       WHEN '1,3' THEN '散步,读书'
       WHEN '2,3' THEN '爬山,读书'
       WHEN '1,2,3' THEN '散步,爬山,读书'
       ELSE '无'
   END
   AS hobby
,t1.workage,t1.phonenumber,t1.personcardnumber,t1.address,t1.createtime,t1.departmentname,t1.jobname,t1.status,t1.isadmin,t1.other FROM t_user t1 ORDER BY STATUS DESC

1.3 处理null值

-- 处理完null 再转化
SELECT t1.username,t1.hobby ,t1.workage,t1.phonenumber,t1.personcardnumber,t1.address,t1.createtime,t1.departmentname,t1.jobname,

CASE WHEN t1.status IS NULL THEN '2' ELSE t1.status END AS statuss

,t1.isadmin,t1.other FROM t_user t1 ORDER BY STATUS DESC

2.if ...else....

if else语句只有在存储过程，触发器之类的才有，今天突然在sql上当满足某种条件上要取不同的字段值，一开始直接网上查找相关资料，哇，sql上不支持！！！

2.1 IF(expr1,'值1','值2') AS 名称

SELECT t1.username,t1.hobby ,t1.workage,t1.phonenumber,t1.personcardnumber,t1.address,t1.createtime,t1.departmentname,t1.jobname,
IF(t1.status = '1','在职','离职') AS statuss

,t1.isadmin,t1.other FROM t_user t1 ORDER BY STATUS DESC