一文说明白Oracle case when的使用

SQL 里面的case when 表达式能够让开发者把 if ...then...else 的逻辑写到SQL语句里面。并且不需要调用存储过程。其语法如下图:

有两种表达方式，

一种是简单的 CASE 表达式，Oracle 数据库第一个 WHEN ...THEN是否对，其中 expr 等于 comparison_expr 并返回 return_expr。如果没有 WHEN ...THEN 对满足此条件，并且存在 ELSE 子句，则 Oracle 返回 else_expr。否则，Oracle 将返回 null

另一种是搜索的 CASE 表达式中，Oracle 从左到右搜索，直到找到出现的条件为 true，然后返回 return_expr。如果未发现任何条件为 true，并且存在 ELSE 子句，则 Oracle 将返回 else_expr。否则，Oracle 将返回 null

直接例子：

首先构造两个表和数据

 create table EXAM_RESULTS
(
  student_id      INTEGER not null,
  exam_id         INTEGER not null,
  percent_correct NUMBER(5,2)
)

create table EXAM_GRADES
(
  grade       CHAR(1) not null,
  lower_bound NUMBER,
  upper_bound NUMBER,
  outcome     VARCHAR2(10) not null
) 

insert into exam_grades values ( 'A', 90,   null, 'Pass' );
insert into exam_grades values ( 'B', 80,   90,   'Pass' );
insert into exam_grades values ( 'C', 70,   80,   'Pass' );
insert into exam_grades values ( 'D', 60,   70,   'Fail' );
insert into exam_grades values ( 'E', 50,   60,   'Fail' );
insert into exam_grades values ( 'F', null, 50,   'Fail' );

insert into exam_results values ( 1, 1, 95 );
insert into exam_results values ( 2, 2, 85 );
insert into exam_results values ( 3, 3, 75 );
insert into exam_results values ( 4, 4, 65 );
insert into exam_results values ( 5, 5, 55 );
insert into exam_results values ( 6, 6, 45 );

insert into exam_results values ( 0, 1, 100 );
insert into exam_results values ( 1, 0, 100 );
insert into exam_results values ( 0, 0, 100 );
insert into exam_results values ( 0, 2, null );
insert into exam_results values ( 2, 0, null );

1.简单case表达式

SELECT t.exam_id,
       CASE t.exam_id
         WHEN 1 THEN
          'SQL'
         WHEN 2 THEN
          'JAVA'
         WHEN 3 THEN
          'PYTHON'
         WHEN 4 THEN
          'Javascript'
         ELSE
          'other'
       END exam_name
  FROM exam_results t
 GROUP BY t.exam_id
 ORDER BY t.exam_id;

上面语句有点类似 decode 函数的使用

SELECT t.exam_id,
       decode(exam_id, 1, 'SQL', 2, 'Java', 3, 'Python', 4, 'JavaScript') exam_name
  FROM exam_results t
 GROUP BY t.exam_id
 ORDER BY t.exam_id;

还有个类似的写法：

SELECT t.exam_id,
       CASE
         WHEN t.exam_id = 1 THEN
          'SQL'
         WHEN t.exam_id = 2 THEN
          'JAVA'
         WHEN t.exam_id = 3 THEN
          'PYTHON'
         WHEN t.exam_id = 4 THEN
          'Javascript'
         ELSE
          'other'
       END exam_name
  FROM exam_results t
 GROUP BY t.exam_id
 ORDER BY t.exam_id;

如果then后面 'SQL' 和 66 的类型不一样，会报错如下

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SELECT t.exam_id,
       CASE t.exam_id
         WHEN 1 THEN
          'SQL'
         WHEN 2 THEN
          66
         WHEN 3 THEN
          'PYTHON'
         WHEN 4 THEN
          'Javascript'
         ELSE
          'other'
       END exam_name
  FROM exam_results t
 GROUP BY t.exam_id
 ORDER BY t.exam_id;

2.搜索case表达式

SELECT student_id,
       exam_id,
       percent_correct,
       CASE
         WHEN percent_correct >= 90 THEN
          'A'
         WHEN percent_correct >= 80 THEN
          'B'
         WHEN percent_correct >= 70 THEN
          'C'
         WHEN percent_correct >= 60 THEN
          'D'
         WHEN percent_correct >= 50 THEN
          'E'
         ELSE
          'F'
       END grade
  FROM exam_results;

注意case when 是从上到下匹配，如果第一行匹配成功，后面的就不会执行了，这里很容易出现逻辑错误 比如下面的sql

SELECT student_id,
       exam_id,
       percent_correct,
       CASE
         WHEN percent_correct >= 50 THEN
          'A'
         WHEN percent_correct >= 80 THEN
          'B'
         WHEN percent_correct >= 70 THEN
          'C'
         WHEN percent_correct >= 60 THEN
          'D'
         WHEN percent_correct >= 90 THEN
          'E'
         ELSE
          'F'
       END grade
  FROM exam_results;

因为   WHEN percent_correct >= 50 THEN 写在第一行，导致后面的查询结果都出现错误

3.select中使用case

SELECT student_id,
       exam_id,
       percent_correct,
       CASE
         WHEN percent_correct >= 90 THEN
          'A'
         WHEN percent_correct >= 80 THEN
          'B'
         WHEN percent_correct >= 70 THEN
          'C'
         WHEN percent_correct >= 60 THEN
          'D'
         WHEN percent_correct >= 50 THEN
          'E'
         ELSE
          'F'
       END grade
  FROM exam_results;

4.where子句中使用case

SELECT student_id, exam_id, percent_correct
  FROM exam_results
 WHERE CASE
         WHEN percent_correct >= 90 THEN
          'A'
         WHEN percent_correct >= 80 THEN
          'B'
         WHEN percent_correct >= 70 THEN
          'C'
         WHEN percent_correct >= 60 THEN
          'D'
         WHEN percent_correct >= 50 THEN
          'E'
         ELSE
          'F'
       END IN ('A', 'B');

查到等级为A 和B 的记录(有5行记录)

 
5.join后面使用case

SELECT student_id, exam_id, percent_correct, grade, outcome
  FROM exam_results
  JOIN exam_grades
    ON grade = CASE
         WHEN percent_correct >= 90 THEN
          'A'
         WHEN percent_correct >= 80 THEN
          'B'
         WHEN percent_correct >= 70 THEN
          'C'
         WHEN percent_correct >= 60 THEN
          'D'
         WHEN percent_correct >= 50 THEN
          'E'
         ELSE
          'F'
       END;

关联成绩等级表查询结果

6.PL/SQL里面使用case

set serveroutput on

declare
  grade   char(1);
  outcome varchar2(10);
begin
  for rws in ( select * from exam_results ) loop
    case
      when rws.percent_correct >= 90 then
        grade := 'A'; outcome := 'Pass';
      when rws.percent_correct >= 80 then
        grade := 'B'; outcome := 'Pass';
      when rws.percent_correct >= 70 then
        grade := 'C'; outcome := 'Pass';
      when rws.percent_correct >= 60 then
        grade := 'D'; outcome := 'Fail';
      when rws.percent_correct >= 50 then
        grade := 'E'; outcome := 'Fail';
      else
        grade := 'F'; outcome := 'Fail';
    end case;
    dbms_output.put_line ( rws.percent_correct || ' ' || grade || ' ' || outcome );
  end loop;
end;
/

另外下面情况也能使用case

虚拟列中也能使用case子句

alter table exam_results
  add ( grade as ( case
         when percent_correct >= 90 then 'A'
         when percent_correct >= 80 then 'B'
         when percent_correct >= 70 then 'C'
         when percent_correct >= 60 then 'D'
         when percent_correct >= 50 then 'E'
         else 'F'
       end )
  );

SELECT table_name, COLUMN_NAME, data_type, data_default, VIRTUAL_COLUMN
  FROM user_tab_cols
 WHERE table_name = 'EXAM_RESULTS'；

select grade, count(*)
from   exam_results
where  grade in ( 'A', 'B', 'C' )
group  by grade
order  by grade;

跟访问正常的列名一样

函数里面也能使用case when

CREATE OR REPLACE FUNCTION percent_to_grade(percent_correct NUMBER)
  RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
  RETURN CASE WHEN percent_correct >= 90 THEN 'A' WHEN percent_correct >= 80 THEN 'B' WHEN percent_correct >= 70 THEN 'C' WHEN percent_correct >= 60 THEN 'D' WHEN percent_correct >= 50 THEN 'E' ELSE 'F' END;
END;

SELECT student_id,
       exam_id,
       percent_correct,
       percent_to_grade(percent_correct) grade
  FROM exam_results;

case姿势太多，学费了么

 

后续会更新更多SQL 和PLSQL技巧，欢迎关注下面公众号同步更新