Oracle中case when详解

【格式】

        格式一：

        case
        when conditionalExpression_01 then result_01
        when conditionalExpression_02 then result_02
        ......

        when conditionalExpression_n then result_n
        else result_default
        end

        格式二：

        case expression
        when condition_01 then result_01
        when condition_02 then result_02
        ......

        when condition_n then result_n
        else result_default
        end

【说明】

• 若条件conditionalExpression_01值为true，或表达式expression符合条件condition_01，则返回结果result_01，若该条件不满足，则继续往下判断；
• 若条件conditionalExpression_02值为true，或表达式expression符合条件condition_02，则返回结果result_02，若该条件不满足，则继续往下判断；
• 以此类推，若不满足上一个条件，则继续判断是否满足下一个条件；
• 若条件conditionalExpression_n值为true，或表达式expression符合条件condition_n，则返回结果result_n，若该条件不满足，则继续往下判断；
• 若以上条件都不满足，则返回默认值result_default，若省略默认值result_default，则返回null。

【使用形式】

        第一种：

• case when conditionalExpression_01 then result_01 end
• case expression when condition_01 then result_01 end

        释义：当条件conditionalExpression_01为true时，或expression 满足条件condition_01时，返回结果result_01，否则返回null；

        第二种：

• case when conditionalExpression_01 then result_01 else result_default end
• case expression when condition_01 then result_01 else result_default end

        释义：当条件conditionalExpression_01为true时，或expression 满足条件condition_01时，返回结果result_01，否则返回result_default；

        第三种：

• case when conditionalExpression_01 then result_01 when conditionalExpression_02 then result_02 end
• case expression when condition_01 then result_01 when condition_02 then result_02 end

        释义：当条件conditionalExpression_01为true时，或expression 满足条件condition_01时，返回结果result_01，若不满足，则继续判断，当条件conditionalExpression_02为true时，或expression 满足条件condition_02时，返回结果result_02，否则返回null；

        第四种：

• case when conditionalExpression_01 then result_01 when conditionalExpression_02 then result_02 else result_default end
• case expression when condition_01 then result_01 when condition_02 then result_02 else result_default end

        释义：当条件conditionalExpression_01为true时，或expression 满足条件condition_01时，返回结果result_01，若不满足，则继续判断，当条件conditionalExpression_02为true时，或expression 满足条件condition_02时，返回结果result_02，否则返回result_default；

        第五种：

        当条件有n个时，写法参照以上模板。

【创建样例数据】

        先创建数据表，建表语句如下：

-- Create table
create table T_STUDENT_SCORE
(
  studentid  VARCHAR2(30) not null,
  classid    VARCHAR2(30),
  score      NUMBER,
  scorelevel VARCHAR2(30)
)
tablespace JMMCTS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_STUDENT_SCORE
  is '学生考试成绩表（仅做样例使用）';
-- Add comments to the columns 
comment on column T_STUDENT_SCORE.studentid
  is '学生id';
comment on column T_STUDENT_SCORE.classid
  is '班级id';
comment on column T_STUDENT_SCORE.score
  is '考试成绩';
comment on column T_STUDENT_SCORE.scorelevel
  is '成绩评级';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_STUDENT_SCORE
  add constraint PK_T_STUDENT_SCORE primary key (STUDENTID)
  using index 
  tablespace JMMCTS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

        再往表中插入数据，SQL语句如下：

insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId01', 'ClasId01', 50, 'E');
insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId02', 'ClasId01', 60, 'D');
insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId03', 'ClasId01', 70, 'C');
insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId04', 'ClasId02', 80, 'B');
insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId05', 'ClasId02', 90, 'A');
insert into T_STUDENT_SCORE (studentid, classid, score, scorelevel)
values ('StudId06', 'ClasId02', 100, 'S');
commit;

        最后查询表中数据，返回结果如下：

【样例展示1】

        sql语句如下：

select t.*,case when score=100 then '完美' end as fullScoreFlag from T_STUDENT_SCORE t

或
select t.*,case score when 100 then '完美' end as fullScoreFlag from T_STUDENT_SCORE t

        返回结果如下图：若是100分，则返回“完美”，否则返回null。

【样例展示2】

        sql语句如下：

 select t.*,case when score=100 then '完美' else '其他' end as fullScoreFlag from T_STUDENT_SCORE t

或
select t.*,case score when 100 then '完美' else '其他' end as fullScoreFlag from T_STUDENT_SCORE t

        返回结果如下图：若是100分，则返回“完美”，否则返回“其他”。

【样例展示3】

        sql语句如下：

 select t.*,case when score=100 then '完美' when score=90 then '优秀' end as fullScoreFlag from T_STUDENT_SCORE t;

或
select t.*,case score when 100 then '完美' when 90 then '优秀' end as fullScoreFlag from T_STUDENT_SCORE t

        返回结果如下图：若是100分，则返回“完美”，若是90分，则返回“优秀”，否则返回null。

【样例展示4】

        sql语句如下：

 select t.*,case when score=100 then '完美' when score=90 then '优秀' else '其他' end as fullScoreFlag from T_STUDENT_SCORE t;

或
select t.*,case score when 100 then '完美' when 90 then '优秀' else '其他' end as fullScoreFlag from T_STUDENT_SCORE t

        返回结果如下图：若是100分，则返回“完美”，若是90分，则返回“优秀”，否则返回“其他”。

【样例展示5】

        sql语句如下：

select * from T_STUDENT_SCORE 
order by 
case 
 when scoreLevel='S' then 0 
 when scoreLevel='A' then 1 
 when scoreLevel='B' then 2 
 when scoreLevel='C' then 3  
 when scoreLevel='D' then 4 
else 5 end
asc

        返回结果如下图：按成绩评级由高到低排序。

【样例展示6】

        sql语句如下：

 select 
count(*),
case 
 when scoreLevel='S' then '不错'
 when scoreLevel='A' then '不错'
 when scoreLevel='B' then '一般'
 when scoreLevel='C' then '一般'
 when scoreLevel='D' then '较差'
 when scoreLevel='E' then '较差'
 else '其他' end as scoreRemark
from T_STUDENT_SCORE
group by 
case 
 when scoreLevel='S' then '不错'
 when scoreLevel='A' then '不错'
 when scoreLevel='B' then '一般'
 when scoreLevel='C' then '一般'
 when scoreLevel='D' then '较差'
 when scoreLevel='E' then '较差'
 else '其他' end

        返回结果如下图：按成绩评级分组统计学生数量。

 

【样例展示7】

        sql语句如下：

select * from T_STUDENT_SCORE where 
(case 
 when scoreLevel='S' then '不错'
 when scoreLevel='A' then '不错'
 when scoreLevel='B' then '一般'
 when scoreLevel='C' then '一般'
 when scoreLevel='D' then '较差'
 when scoreLevel='E' then '较差'
 else '其他' end)='不错'

        返回结果如下图：按成绩评级作为条件筛选成绩不错的学生。