CASE WHEN

原创 2007年09月27日 10:26:00
case when x = y then a else b end

case when x < y then a when x = y then b else c end

case XYZ when 'foo' then 'moo' else 'bar' end

The following little SQL script demonstrates the use of CASE WHEN. 
 test_case_when (
  a varchar2(5),
  b varchar2(5)
);

insert into test_case_when values ('*','*');
insert into test_case_when values ('+','+');
insert into test_case_when values ('-','-');
insert into test_case_when values ('.','.');

 a, 
  case
    when b = '*' then 'star'
    when b = '+' then 'plus'
    when b = '-' then 'minus' 
    else '????'
  end 
from test_case_when;
This select statement produces the following output:
A     CASEW
----- -----
*     star
+     plus
-     minus
.     ????
drop table test_case_when;
 
ORACLE官方文档说明:

CASE Expressions

There are two types of expressions used in CASE statements: simple and searched. These expressions correspond to the type of CASE statement in which they are used. See "Using CASE Statements".

Simple CASE expression

A simple CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a procedure call. The CASE expression uses a selector, an expression whose value determines which alternative to return.

A CASE expression has the form illustrated in Example 2-26. The selector (grade) is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. If there are no matches, then the optional ELSE clause is performed.

Example 2-26 Using the WHEN Clause With a CASE Statement

DECLARE
   grade CHAR(1) := 'B';
   appraisal VARCHAR2(20);
BEGIN
   appraisal :=
      CASE grade
         WHEN 'A' THEN 'Excellent'
         WHEN 'B' THEN 'Very Good'
         WHEN 'C' THEN 'Good'
         WHEN 'D' THEN 'Fair'
         WHEN 'F' THEN 'Poor'
         ELSE 'No such grade'
      END;
   DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/

The optional ELSE clause works similarly to the ELSE clause in an IF statement. If the value of the selector is not one of the choices covered by a WHEN clause, the ELSE clause is executed. If no ELSE clause is provided and none of the WHEN clauses are matched, the expression returns NULL.

Searched CASE Expression

A searched CASE expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 2-27.

A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.

Example 2-27 Using a Search Condition With a CASE Statement

DECLARE
   grade CHAR(1) := 'B';
   appraisal VARCHAR2(120);
   id NUMBER := 8429862;
   attendance NUMBER := 150;
   min_days CONSTANT NUMBER := 200;
FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS
   BEGIN RETURN TRUE; END;
BEGIN
   appraisal :=
      CASE
         WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
-- Have to test this condition early to detect good students with bad attendance
         WHEN grade = 'F' OR attendance < min_days 
            THEN 'Poor (poor performance or bad attendance)'
         WHEN grade = 'A' THEN 'Excellent'
         WHEN grade = 'B' THEN 'Very Good'
         WHEN grade = 'C' THEN 'Good'
         WHEN grade = 'D' THEN 'Fair'
         ELSE 'No such grade'
      END;
   DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal);
END;
/

The search conditions are evaluated sequentially. The BOOLEAN value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. After any WHEN clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields TRUE, the optional ELSE clause is executed. If no WHEN clause is executed and no ELSE clause is supplied, the value of the expression is NULL.

Case when的用法

大家都知道Case when的用法,一旦满足了某一个WHEN ,则这一条数据就会退出CASE WHEN,而不再考虑其他CASE ,文章来详细的介绍了case when的用法并举例说明了。   Ca...
  • houpengfei111
  • houpengfei111
  • 2013年09月05日 15:02
  • 25352

SQl CASE WHEN 语句的嵌套使用方式

case具有两种格式。简单case函数和case搜索函数。   1.简单case函数   case sex when ’1’ then ’男’ when ’2’ then ’女’else...
  • andyzhaojianhui
  • andyzhaojianhui
  • 2015年07月16日 10:54
  • 5518

一次有趣的case when函数使用经历

一次在做图表的时候,需要对查询的数据做些处理,也就是要做行列的转换工作,原有的数据格式是这样的: area recordmethod num 00 1 20 00 0 ...
  • henu2009220176
  • henu2009220176
  • 2015年07月19日 15:07
  • 1594

Case_When用法

  • 2012年03月09日 15:42
  • 1.77MB
  • 下载

详细解析 mysql 中的 case when 语句的使用

  • 2009年03月25日 14:59
  • 6KB
  • 下载

SQL__case_when_then_的用法

  • 2013年11月21日 16:46
  • 24KB
  • 下载

case when 写法及拼接字段

  • 2016年06月03日 21:51
  • 15KB
  • 下载

case when then

  • 2013年06月03日 14:20
  • 720B
  • 下载

Mysql系列课程--第五章 高级查询 表连接 子查询 case when

一.表连接1.内连接1.1等值内连接 /李文辉是哪个班的/select s.s_name,c.c_name from student s inner join class c on s.c_no =...
  • u012843355
  • u012843355
  • 2017年11月25日 17:26
  • 39

用sum case when做分类统计sql优化

背景:本来一个页面涉及到多个类别的统计,最初的代码是每一个类别做一个sql来count出数目,可想而知,最后的加载速度和体验都是不好,能明显感觉至少5、6秒甚至更多的响应时间 之前的代码: //1...
  • rear0312
  • rear0312
  • 2017年11月23日 15:46
  • 86
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:CASE WHEN
举报原因:
原因补充:

(最多只允许输入30个字)