case when 语法
select t.* ,
case (selectcolumn)
when expression1 then result1
when expression2 then result2
...
when expressionN then resultN
else result
end columnname //如需返回新列,则‘newcolumnname’;返回查询表原有列,则直接写列名
from tablename t
case when 用例
-
针对查询表某一列判断
//case when 返回新列 select stu.*, case stu.sex when '0' then '男' when '1' then '女' else '未填写性别' end 'studentsex' from student stu; //返回查询表原有列 select stu.no, stu.name, case when stu.sex = 0 then '男' when stu.sex = 1 then '女' else '未填写性别' end sex from student stu
-
对查询表 多列进行判断
select stu.no, stu.username, stu.sex, case when stu.resume is null then '简历为空' when stu.classes is null then '班级信息不完善' else '信息基本完善' end 'userresult' from student stu