一文说明白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
直接例子：
首先构造两个表和数据

1. create table EXAM_RESULTS
2. (
3.   student_id      INTEGER not null,
4.   exam_id         INTEGER not null,
5.   percent_correct NUMBER(5,2)
6. )
8. create table EXAM_GRADES
9. (
10.   grade       CHAR(1) not null,
11.   lower_bound NUMBER,
12.   upper_bound NUMBER,
13.   outcome     VARCHAR2(10) not null
14. )
16. insert into exam_grades values ( 'A', 90,   null, 'Pass' );
17. insert into exam_grades values ( 'B', 80,   90,   'Pass' );
18. insert into exam_grades values ( 'C', 70,   80,   'Pass' );
19. insert into exam_grades values ( 'D', 60,   70,   'Fail' );
20. insert into exam_grades values ( 'E', 50,   60,   'Fail' );
21. insert into exam_grades values ( 'F', null, 50,   'Fail' );
23. insert into exam_results values ( 1, 1, 95 );
24. insert into exam_results values ( 2, 2, 85 );
25. insert into exam_results values ( 3, 3, 75 );
26. insert into exam_results values ( 4, 4, 65 );
27. insert into exam_results values ( 5, 5, 55 );
28. insert into exam_results values ( 6, 6, 45 );
30. insert into exam_results values ( 0, 1, 100 );
31. insert into exam_results values ( 1, 0, 100 );
32. insert into exam_results values ( 0, 0, 100 );
33. insert into exam_results values ( 0, 2, null );
34. 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技巧，接待关注下面公众号同步更新

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。