SQL之CASE WHEN用法详解

目录

• 一、简单CASE WHEN函数：
• 二、CASE WHEN条件表达式函数
• 三、常用场景
• • 场景1：不同状态展示为不同的值
  • 场景2：统计不同状态下的值
  • 场景3：配合聚合函数做统计
  • 场景4：CASE WHEN中使用子查询
  • 场景5：经典行转列，结合max聚合函数

  一、简单CASE WHEN函数：

  CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
  # 使用 IF 函数进行替换
  IF(SCORE = 'A', '优', '不及格')
  

  THEN后边的值与ELSE后边的值类型应一致，否则会报错。

  如下：

  CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE 0 END’优’和0数据类型不一致则报错：

  [Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

  简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。

  二、CASE WHEN条件表达式函数

  类似JAVA中的IF ELSE语句。

  格式：

  CASE WHEN condition THEN result
   
  [WHEN...THEN...]
   
  ELSE result
   
  END
  

  SQL语言演示：

  CASE 
  	 WHEN SCORE = 'A' THEN '优'
       WHEN SCORE = 'B' THEN '良'
       WHEN SCORE = 'C' THEN '中' 
       ELSE '不及格' END
  # 等同于
  CASE score
      WHEN 'A' THEN '优'
      WHEN 'B' THEN '良'
      WHEN 'C' THEN '中'
      ELSE '不及格' END
  

  condition是一个返回布尔类型的表达式，

  如果表达式返回true，则整个函数返回相应result的值，

  如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

  三、常用场景

  前言

  students表的DDL

  -- auto-generated definition
  create table students
  (
      stu_code  varchar(10) null,
      stu_name  varchar(10) null,
      stu_sex   int         null,
      stu_score int         null
  );
  

  students表的DML

  # 其中stu_sex字段，0表示男生，1表示女生。
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xm', '小明', 0, 88);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xl', '夏磊', 0, 55);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xf', '晓峰', 0, 45);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xh', '小红', 1, 89);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xn', '小妮', 1, 77);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xy', '小一', 1, 99);
  INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES ('xs', '小时', 1, 45);
  

  ---

  energy_test表的DDL

  -- auto-generated definition
  create table energy_test
  (
      e_code  varchar(2)    null,
      e_value decimal(5, 2) null,
      e_type  int           null
  );
  

  energy_test表的DML

  # 其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 28.50, 0);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 23.50, 1);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 28.12, 2);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 12.30, 0);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('北京', 15.46, 1);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 18.88, 0);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 16.66, 1);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 19.99, 0);
  INSERT INTO energy_test (e_code, e_value, e_type) VALUES ('上海', 10.05, 0);
  

  ---

  p_price表的DDL

  -- auto-generated definition
  create table p_price
  (
      p_price decimal(5, 2) null comment '价格',
      p_level int           null comment '等级',
      p_limit int           null comment '阈值'
  )
      comment '电能耗单价表';
  

  p_price表的DML

  INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.20, 0, 10);
  INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.70, 1, 30);
  INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (2.50, 2, 50);
  

  ---

  user_col_comments 表的DDL

  -- auto-generated definition
  create table user_col_comments
  (
      column_name varchar(50)  null comment '列名',
      comment     varchar(100) null comment '列的备注'
  );
  

  user_col_comments 表的DML

  INSERT INTO test.user_col_comments (column_name, comment) VALUES ('SHI_SHI_CODE', '设施编号');
  INSERT INTO test.user_col_comments (column_name, comment) VALUES ('SHUI_HAO', '水耗');
  INSERT INTO test.user_col_comments (column_name, comment) VALUES ('RE_HAO', '热耗');
  INSERT INTO test.user_col_comments (column_name, comment) VALUES ('YAN_HAO', '盐耗');
  INSERT INTO test.user_col_comments (column_name, comment) VALUES ('OTHER', '其他');
  

  场景1：不同状态展示为不同的值

  有分数score，score=60返回及格，score>=80返回优秀
  

  # 有分数score，score=60返回及格，score>=80返回优秀
  SELECT
      stu_name,
      (CASE WHEN stu_score = 60 AND stu_score = 80 THEN '优秀'
          ELSE '异常' END) AS REMARK
  FROM students;
  

  注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：
  CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

  场景2：统计不同状态下的值

  现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。其中stu_sex字段，0表示男生，1表示女生。
  

  SELECT
  	sum(CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
  	sum(CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
  	sum(CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
  	sum(CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
  FROM
  	students;
  

  输出结果如下：

  

  注意点：

  • 用的是 ：sum 而不是count
  • THEN 1 ELSE 0的位置不能改变：否则会有以下效果：

    sum(CASE WHEN stu_sex = 0 THEN '1' ELSE '0' END) AS '男性',
    改变了 
    sum(CASE WHEN stu_sex = 0 THEN '0' ELSE '1' END) AS '女性'：
    

  • 字符 ‘0’ 和 数值 0，使用 都是一样的

    场景3：配合聚合函数做统计

    现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

    有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗
    

    select e_code,
           sum(case when e_type = 0 then e_value else 0 end) as '水耗',
           sum(case when e_type = 1 then e_value else 0 end) as '电耗',
           sum(case when e_type = 2 then e_value else 0 end) as '热耗'
    from energy_test
    group by e_code;
    

    输出结果如下：

    

    场景4：CASE WHEN中使用子查询

    根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。

    当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…
    

    energy_test 我修改了e_type 为1的值的两条数据的e_value。
    

    select e_code, e_value,
         (CASE WHEN e_value  (SELECT p_limit FROM p_price WHERE p_level = 0) AND e_value  (SELECT p_limit FROM p_price WHERE p_level = 1) AND e_value