case when then else end 用法

其实这种用法在平常的操作中很常用

　　首先来展示下他的效果：

create table student(
       sid int ,
       sname varchar2(20),
       sex varchar2(20) 
       );
 
create sequence seq_student_id increment by 1 start with 1 NOMAXVALUE;    

insert into student values(seq_student_id.nextval,'yaobo','man');
insert into student values(seq_student_id.nextval,'yaobo2','man'); 
insert into student values(seq_student_id.nextval,'yaobo3','woman'); 
insert into student values(seq_student_id.nextval,'yaobo4','man'); 
insert into student values(seq_student_id.nextval,'yaobo5','woman'); 
insert into student values(seq_student_id.nextval,'yaobo6','man33');

（1）第一种写法
select case 
         when sex = 'man' then
          '男'
         when sex = 'woman' then
          '女'
         else
          '人妖'
       end
  from student;

这两种写法的所得的结果都是一样的

（2）第二种写法
select case sex
          when 'man' then
          '男'
          when 'woman' then
          '女'
         else
          '人妖'
       end
  from student;

这个满足了when后面的第一个条件，后面的条件就会自动跳过