[Mysql] CASE表达式

山茶花开时。

已于 2023-05-23 21:02:21 修改

阅读量3.7k

点赞数 8

分类专栏： Mysql 文章标签： mysql sql

于 2022-04-01 23:12:16 首次发布

本文链接：https://blog.csdn.net/Hudas/article/details/123901676

版权

Mysql 专栏收录该内容

55 篇文章 66 订阅

订阅专栏

1.引言

导入数据

DROP TABLE IF EXISTS staff_situation; 
CREATE TABLE staff_situation( 
company VARCHAR(8), 
gender VARCHAR(8), 
num INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
staff_situation (company,gender,num) 
VALUE ('A','male',163) 
,('A','female',142) 
,('B','male',98) 
,('B','female',116) 
,('C','male',234) 
,('C','female',196);

staff_situation表(员工情况表)

该表记录了不同公司、不同性别的员工人数，接下来需要统计该表的信息，输出如下结果:

如果没有学过CASE WHEN，可以考虑使用多表连接，分别统计男性和女性数据，然后将这些数据连接在一起，得到最终结果

SELECT m.company, m.num_male, f.num_female 
FROM (SELECT company, SUM(num) AS num_male 
      FROM staff_situation 
      WHERE gender = 'male' 
      GROUP BY company) AS m 
INNER JOIN (SELECT company, SUM(num) AS num_female 
            FROM staff_situation 
            WHERE gender = 'female' 
            GROUP BY company) AS f 
ON m.company = f.company;

接下来使用CASE WHEN替换上面的代码:

SELECT company, 
       SUM(CASE WHEN gender='male' THEN num ELSE 0 END) AS num_male,
       SUM(CASE WHEN gender='female' THEN num ELSE 0 END) AS num_female 
FROM staff_situation 
GROUP BY company;

通过对比发现，在合适的情况下灵活使用CASE WHEN可以减少SQL代码的书写量

使用CASE表达式的情况:当需要根据一些字段的值生成新的列或者逻辑判断较为复杂时，可能会使用CASE WHEN

CASE表达式是在区分情况时使用的，这种情况的区分在编程中通常称为(条件)分支

在对SELECT语句的结果进行编辑时，CASE表达式能够发挥较大作用

2.CASE表达式类型

CASE WHEN通常可以分为简单CASE表达式和搜索CASE表达式

2.1简单CASE表达式

简单CASE表达式的语法结构:
CASE <表达式>
     WHEN <值1> THEN <结果1>
     WHEN <值2> THEN <结果2>
     ...
     ELSE <结果n>
END
通过类比编程语言中的IF条件分支语句结构，当<表达式>的值为<值1>时，输出结果<结果1>，当<表达式>的值为<值2>时，输出结果<结果2>，以此类推。当不满足以上所有条件时，输出默认的<结果n>

案例

导入数据

DROP TABLE IF EXISTS personnel_info; 
CREATE TABLE personnel_info( 
personnel_id VARCHAR(8), 
personnel_gender VARCHAR(8) 
) 
ENGINE = InnoDB
DEFAULT CHARSET = utf8; 
INSERT INTO 
personnel_info (personnel_id,personnel_gender) 
VALUE ('p001','male') 
,('p002','female') 
,('p003','female') 
,('p004','male') 
,('p005','female');

personnel_info表(人员信息表)

personnel_id: 人员ID

personnel_gender: 人员性别

问题:使用SQL将人员的性别字段值进行编码，以0表示female，以1表示male

SELECT personnel_id,
       CASE personnel_gender 
            WHEN 'female' THEN '0' 
            WHEN 'male' THEN '1' 
            ELSE '未知' 
       END AS personnel_gender 
FROM personnel_info;

结果展示:

上述代码相当于使用简单CASE WHEN语句新生成了一列编码字段，在SELECT之后使用CASE WHEN语句，相当于将整个CASE WHEN作为一个新生成的字段

2.2搜索CASE表达式

搜索CASE表达式的语法结构:
CASE WHEN <表达式 = 值1> THEN <结果1>
     WHEN <表达式 = 值2> THEN <结果2>
     ...
     ELSE <结果n>
END
搜索CASE表达式会从最初的WHEN子句中的<表达式 = 值1>进行求值，如果结果为真(TRUE)，那么就返回THEN子句中的<结果1>，CASE表达式的执行到此为止;如果结果不为真(FALSE)，那么就跳转到下一条WHEN子句的求值之中; 如果直到最后的WHEN子句为止,返回结果都不为真，那么就会返回ELSE中的<结果n>，执行终止

结合上述案例,使用搜索CASE表达式替代简单CASE表达式，其代码如下所示:

SELECT personnel_id,
       CASE WHEN personnel_gender = 'female' THEN '0' 
            WHEN personnel_gender = 'male' THEN '1' 
            ELSE '未知' 
       END AS personnel_gender 
FROM personnel_info;

对于使用简单CASE表达式难以完成的情况，使用搜索CASE表达式则很方便

问题: 针对下述公式，根据提供的x值求解相对应的y值

导入数据

DROP TABLE IF EXISTS func_expression; 
CREATE TABLE func_expression( 
x INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
func_expression (x) 
VALUE (-3),(-2),(-1),(0),(1),(2),(3);

func_expression表

在上述func_expression表中，x的取值分别为-3，-2，-1，0，1，2，3

SELECT x,
       CASE WHEN x<-1 THEN x+1 
            WHEN x>=-1 THEN 2*x 
            ELSE 0 
       END AS y 
FROM func_expression;

结果展示 :

搜索CASE表达式可以实现和简单CASE表达式所实现的相同效果，一般推荐使用搜索CASE表达式

搜索CASE表达式包含了简单CASE表达式的全部功能

简单CASE表达式比搜索CASE表达式简单，但是会受到条件的约束，因此通常情况下都会使用搜索CASE表达式

3.CASE表达式注意点

1.养成写ELSE子句的习惯

ELSE子句指定了应该如何处理不满足WHEN子句中的条件的记录，ELSE子句也可以省略不写，与END不同，ELSE子句是可选的，不写也不会出错(但还是希望别省略)

如果CASE表达式里没有明确指定ELSE子句，执行结果会被默认地处理成 ELSE NULL，即CASE表达式的执行结果是NULL

虽然ELSE子句可以省略不写，但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上ELSE子句(即便是在结果可以为NULL的情况下)

2.不要忘了写END

CASE表达式中的END不能省略

3.使用WHEN子句时要注意条件的排他性

在编写SQL语句的时候需要注意，在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，而剩余的WHEN子句会被忽略，为了避免引起不必要的混乱，使用WHEN子句时要注意条件的排他性

4.统一各分支返回的数据类型

4.案例练习

案例1:在UPDATE语句里进行条件分支

导入数据

DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `salary` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `salaries` VALUES ('Harry', 220000.00);
INSERT INTO `salaries` VALUES ('Jack', 270000.00);
INSERT INTO `salaries` VALUES ('Lee', 290000.00);
INSERT INTO `salaries` VALUES ('Odin', 300000.00);

salaries表

问题:假设现在需要根据以下条件对该表的数据进行更新

1.对当前工资为30万日元以上的员工，降薪10%

2.对当前工资为25万日元以上且不满28万日元的员工，加薪20%

-- 用CASE表达式写正确的更新操作 
UPDATE Salaries 
SET salary = CASE WHEN salary >= 300000 THEN salary * 0.9 
                  WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2 
                  ELSE salary 
             END;

结果展示:

案例2:主键值调换

导入数据

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `p_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `test` VALUES ('a', '1', '一');
INSERT INTO `test` VALUES ('b', '2', '二');
INSERT INTO `test` VALUES ('c', '3', '三');

test表

问题:调换主键值a和b

-- 方法1:不用CASE表达式
-- 将a转换为中间值d 
UPDATE test
SET p_key = 'd' 
WHERE p_key = 'a'; 
-- 将b调换为a 
UPDATE test 
SET p_key = 'a'
WHERE p_key = 'b'; 
-- 将d调换为b 
UPDATE test
SET p_key = 'b' 
WHERE p_key = 'd';

-- 方法2:使用CASE表达式
-- 用CASE表达式调换主键值 
UPDATE test 
SET p_key = CASE WHEN p_key = 'a' THEN 'b' 
                 WHEN p_key = 'b' THEN 'a' 
                 ELSE p_key 
            END 
WHERE p_key IN('a', 'b');

结果展示:

案例3:表之间的数据匹配

在CASE表达式里，我们可以使用BETWEEN、LIKE和 <、 > 等便利的谓词组合，以及能嵌套子查询的IN和EXISTS 谓词,CASE表达式具有非常强大的表达能力

导入数据

DROP TABLE IF EXISTS `coursemaster`;
CREATE TABLE `coursemaster`  (
  `course_id` int(11) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `coursemaster` VALUES (1, '会计入门');
INSERT INTO `coursemaster` VALUES (2, '财务知识');
INSERT INTO `coursemaster` VALUES (3, '薄记考试');
INSERT INTO `coursemaster` VALUES (4, '税务师');

DROP TABLE IF EXISTS `opencourses`;
CREATE TABLE `opencourses`  (
  `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `opencourses` VALUES ('200706', 1);
INSERT INTO `opencourses` VALUES ('200706', 3);
INSERT INTO `opencourses` VALUES ('200706', 4);
INSERT INTO `opencourses` VALUES ('200707', 4);
INSERT INTO `opencourses` VALUES ('200708', 2);
INSERT INTO `opencourses` VALUES ('200708', 4);

coursemaster表

opencourses表

问题:用上述两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程(检查表opencourses中的各月里有表coursemaster中的哪些课程)

-- 表的匹配:使用IN谓词 
SELECT course_name, 
       CASE WHEN course_id IN(SELECT course_id FROM opencourses WHERE month = 200706) THEN '○' ELSE '×' END AS "6 月", 
       CASE WHEN course_id IN(SELECT course_id FROM opencourses WHERE month = 200707) THEN '○' ELSE '×' END AS "7 月", 
       CASE WHEN course_id IN(SELECT course_id FROM opencourses WHERE month = 200708) THEN '○' ELSE '×' END AS "8 月" 
FROM coursemaster;

结果展示:

案例4:将已有编号方式转换为新的方式并统计

导入数据

DROP TABLE IF EXISTS `populations`;
CREATE TABLE `populations`  (
  `place_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `population` int(255) DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `populations` VALUES ('深圳', 100);
INSERT INTO `populations` VALUES ('广州', 200);
INSERT INTO `populations` VALUES ('珠海', 150);
INSERT INTO `populations` VALUES ('中山', 200);
INSERT INTO `populations` VALUES ('合肥', 300);
INSERT INTO `populations` VALUES ('马鞍山', 100);
INSERT INTO `populations` VALUES ('芜湖', 200);
INSERT INTO `populations` VALUES ('台州', 400);
INSERT INTO `populations` VALUES ('上海', 50);

populations表

转换成如下:

-- 方法1
-- 这里的关键在于将SELECT子句里的CASE表达式复制到GROUP BY子句里
-- 必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式
SELECT CASE place_name 
       WHEN '深圳' THEN '广东' 
       WHEN '广州' THEN '广东' 
       WHEN '珠海' THEN '广东' 
       WHEN '中山' THEN '广东' 
       WHEN '合肥' THEN '安徽' 
       WHEN '马鞍山' THEN '安徽' 
       WHEN '芜湖' THEN '安徽' 
       ELSE '其他' END AS 地区名, 
SUM(population) AS 人口
FROM populations
GROUP BY  CASE place_name 
          WHEN '深圳' THEN '广东' 
          WHEN '广州' THEN '广东' 
          WHEN '珠海' THEN '广东' 
          WHEN '中山' THEN '广东' 
          WHEN '合肥' THEN '安徽' 
          WHEN '马鞍山' THEN '安徽' 
          WHEN '芜湖' THEN '安徽' 
          ELSE '其他' END; 

-- 方法2(对方法1简化)
-- 这里的GROUP BY子句使用的是SELECT子句里定义的列的别称——地区名
SELECT CASE place_name 
       WHEN '深圳' THEN '广东' 
       WHEN '广州' THEN '广东' 
       WHEN '珠海' THEN '广东' 
       WHEN '中山' THEN '广东' 
       WHEN '合肥' THEN '安徽' 
       WHEN '马鞍山' THEN '安徽' 
       WHEN '芜湖' THEN '安徽' 
       ELSE '其他' END AS 地区名, 
SUM(population) AS 人口
FROM populations
GROUP BY  地区名;

结果展示: