mysql中 case when then end的用法

含义

我理解的case when then end 等价于java 中的if else 

举栗 

学生表 id, name, sex, grade 

离校学生表 id, stu_id

需求

成绩 男生大于70 女生大于 60的学生

select * from student where grade > (case when sex = 0 then 60 when sex = 1 then 70 end)

查看全部学生离校情况

select s.*, case when l.id is null then '未离校' else  '已离校' end as is_leave 
    from student s left join leave_stu l on s.id = l.stu_id 

查看女生离校情况

select * from ( 
    select s.*, case when l.id is null then '未离校' else  '已离校' end as is_leave from student s left join leave_stu l on s.id = l.stu_id 
) r where r.sex = 0