玩儿转 case when then 基础篇

夜猫nightcat

已于 2022-05-11 18:30:18 修改

阅读量595

点赞数 8

分类专栏： MySQL实战小例子文章标签： mysql java dba

于 2022-05-11 12:57:43 首次发布

本文链接：https://blog.csdn.net/m0_69661182/article/details/124696141

版权

MySQL实战小例子专栏收录该内容

6 篇文章 24 订阅 ¥19.90 ¥99.00

订阅专栏

超级会员免费看

在这里插入图片描述

🚩 写在前面

这是《MySQL实战小例子》的第一篇文章，希望朋友们在这个系列中保持动手的习惯，有些算法题，案例实战，可能我们看起来会了，但是不动手，到了工作实战中就不能快速、准确的用起来，这就使得学习效果大打折扣，一定要多动手练习，然后在学习和工作中检验效果。

🚩 MySQL 中 case when then 是什么？

Java 中的 switch case 语句，大家都非常熟悉了:
将switch中的值，与case中的值进行匹配

如果能匹配上，就执行对应case的代码
如果执行的过程中没遇到break ，就接着执行后边的case，否则结束分支语句
如果一直没遇到break，就执行default语句
如果没匹配上，就执行default语句

❤ MySQL中的 case when then

MySQL中的case when then 和Javaswitch case 差不多，也是流程控制语句.

🚀 两种形式

MySQL中的case when then 流程控制语句有两种形式

第一种形式

CASE 表达式
    WHEN  条件表达式1 THEN 值1 或者 表达式1
    WHEN  条件表达式2 THEN 值2 或者 表达式2
    ELSE 其它值 
END

第二种形式

CASE
    WHEN 条件表达式1 THEN 值1 或者 表达式1
    WHEN 条件表达式2 THEN 值2 或者 表达式2
    ELSE 其它值
END

🚀 抛开形式看本质

其实大家不用拘泥于形式，可以这么理解:就是when后边是条件表达式 then后边是你想执行的语句，条件表达式的话就可能是一个条件或者多个条件，比如一条记录有A,B,C三个字段，那条件表达式就可能是(A =1 and B=2 ) or C-=3 这种情况，接着then后边可以是一个值，也可以是一个运算表达式。
对于else子句来说也是一样的，后边可以是值，也可以是表达式。
下面我们进入实战小例子：

🚩 实战小例子

❤ 学完就练，基础篇实战

🚀 结构和数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_student_score
-- ----------------------------
DROP TABLE IF EXISTS `tb_student_score`;
CREATE TABLE `tb_student_score`  (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `type` tinyint(1) NULL DEFAULT NULL COMMENT '主要加分类型',
  `sub_type` tinyint(1) NULL DEFAULT NULL COMMENT '次要加分类型',
  `base_score` int(11) NULL DEFAULT NULL COMMENT '基本分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_student_score
-- ----------------------------
INSERT INTO `tb_student_score` VALUES (1, '张三', 1, 0, 10);
INSERT INTO `tb_student_score` VALUES (2, '李四', 0, 1, 10);
INSERT INTO `tb_student_score` VALUES (3, '王五', 1, 0, 10);
INSERT INTO `tb_student_score` VALUES (4, '赵六', 1, 1, 10);

SET FOREIGN_KEY_CHECKS = 1;

🚀 问题背景描述

执行上边的SQL语句，我们现在的需求是：计算每个人的最终得分，其中每个人的基础分是10分，规则如下：

如果主要加分类型的值是1，那么就给该学生加10分
如果主要加分类型的值是0，那么就给该学生加5分
如果主要加分类型的值既不是0，也不是1，那么就给该学生减5分
额外说明，在本小题中，不涉及次要加分类型

🚀 开始写SQL

我们来看一下，如果直接查询所有学生的iid,姓名,分数，没有其它任何附加条件的话，那么直接写就好了

select id,name,base_score from tb_student_score;

而我们现在题目中的要求是，对每一条记录根据上面说的规则进行加工，然后返回对应的结果，我们用case when then 的第一种形式来尝试一下

SELECT
	id,
	NAME,
CASE
		type 
		WHEN 1 THEN
		base_score + 10 
		WHEN 0 THEN
		base_score + 5 ELSE base_score - 5 
	END AS base_score 
FROM
	tb_student_score t;

观察上边的语句，注意：在Java中我们对一个int 类型变量进行+10 操作的时候，我们需要这样写：

int a =0;
// 给a的值加10
a= a+10;
//或者这样写
a += 10;

但是在SQL中不需要这么麻烦，直接对对应的字段进行操作就行，不用接收它.
我们用第二种形式来做一下这道题，如果忘了可以返回去再看一下:

SELECT
id,
NAME,
CASE
		
		WHEN type = 1 THEN
		base_score + 10 
		WHEN type = 0 THEN
		base_score + 5 ELSE base_score - 5 
	END AS base_score 
FROM
	tb_student_score t;

我们可以看到 case when then这个语句的本质其实就是判断一个或者多个列的运算结果，然后通过结果走不同的逻辑，来改变字段的值的一种结构

🚀 题目升级

🚀 问题背景描述

我们知道，在计算机组成原理中，我们学习过一种运算：异或运算,它的规则是这样的：
对于位运算中两个数的二进制表示的各个位而言，两个位相同时运算结果是0，不同时运算结果是1,比如1和2进行异或运算

1的二进制 01
2的二进制 10
两者异或  11
转成10进制 1*2+1*1 = 3

那么我们的需求变化如下：

计算表中学生的最后分数，对type列和sub_type列的值进行异或运算
如果异或运算的结果是0，那么对该同学的总分减掉5分
如果异或运算的结果是1，那么对该同学的总分加10分
已知type和sub_type列的取值只能取 0 和 1

🚀 开始写SQL

解题思路：在select子句中对type 和 sub_type进行异或运算，根据结果做相应的处理

case when then的第一种形式解题

SELECT
	id,
	NAME,
CASE
		type 
		WHEN type ^ sub_type = 0 THEN
		base_score - 5 
		WHEN type ^ sub_type = 1 THEN
		base_score + 10 ELSE base_score 
	END AS base_score 
FROM
	tb_student_score t;

case when then的第二种形式解题

ELECT
	id,
	NAME,
CASE
		
		WHEN type ^ sub_type = 0 THEN
		base_score - 5 
		WHEN type ^ sub_type = 1 THEN
		base_score + 10 ELSE base_score 
	END AS base_score 
FROM
	tb_student_score t;

🚀 阶段总结

通过上面这两个例子，我们必须明确两点内容：

WHEN 后边的是判断表达式，可以一个条件，也可以是多个条件，甚至可以是值A与某个查询值之间的比较
THEN 后边是SQL处理逻辑的结果，不一定是一个表达式，也有可能是一个查询语句，一个修改语句，甚至是一个BEGIN END 的代码块

🚩 技能准备好了，我要刷 LeetCode

好了，经过了上面基础知识的学习，我们看看算法题中对于case when then这个知识点是怎么考察的

❤ 第一题(中等难度题)

🚀 数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_student
-- ----------------------------
DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_student
-- ----------------------------
INSERT INTO `tb_student` VALUES (1, 'Abbot');
INSERT INTO `tb_student` VALUES (2, 'Doris');
INSERT INTO `tb_student` VALUES (3, 'Emerson');
INSERT INTO `tb_student` VALUES (4, 'Green');
INSERT INTO `tb_student` VALUES (5, 'James');

SET FOREIGN_KEY_CHECKS = 1;

🚀 表说明

id是该表的主键列。
该表的每一行都表示学生的姓名和id
id是一个连续的增量

🚀 需求

交换相邻两人的座位
如果总人数是奇数，那么无需改变最后一人的位置
返回结果按 id 升序排列

🚀 开始写SQL

解答这个题，我们知道，如果总人数是偶数，那么直接对相邻两两交换即可，如果总人数是奇数，那么要对除了最后一个人之外的相邻数据进行交换即可。
那么我们需要

判断一条记录的id是不是奇数，如果是奇数，接着判断它是不是最后一条记录，如果是最后一条记录，那就不需要变换这条记录的id值
如果一条记录id是奇数，但不是最后一条记录，那么对它的id值加1
如果一条记录id是偶数，直接对它的id值减1

SELECT
CASE
		
	WHEN MOD
		( t.id, 2 ) = 1 
		AND t.id = ( SELECT max( id ) FROM tb_student ) THEN
			id 
			WHEN MOD ( t.id, 2 ) = 1 THEN
			id + 1 
			WHEN MOD ( t.id, 2 ) = 0 THEN
			id - 1 
		END AS id,
		`name` 
	FROM
		tb_student t 
ORDER BY
	id

解答这道题，用到了MySQL的一个内置函数mod(x,y),它表示x对y取余的结果。
我们在上边的基础知识里说到,when后边可能有多个判断条件，判断条件中的值可能是一个查询语句的结果，这里不再赘述。

❤ 第二题（简单题）

🚀 数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_salary
-- ----------------------------
DROP TABLE IF EXISTS `tb_salary`;
CREATE TABLE `tb_salary`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` enum('m','f') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_salary
-- ----------------------------
INSERT INTO `tb_salary` VALUES (1, 'A', 'm', 2500.00);
INSERT INTO `tb_salary` VALUES (2, 'B', 'f', 1500.00);
INSERT INTO `tb_salary` VALUES (3, 'C', 'm', 5500.00);
INSERT INTO `tb_salary` VALUES (4, 'D', 'f', 500.00);

SET FOREIGN_KEY_CHECKS = 1;

🚀 表说明

id 是这个表的主键
sex 这一列的值是 ENUM 类型，只能从 (‘m’, ‘f’) 中取

🚀 需求

请你编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ （即，将所有 ‘f’ 变为 ‘m’ ，反之亦然），
仅使用单个 update 语句，且不产生中间临时表。
注意，你必须仅使用一条 update 语句，且不能使用 select 语句。

🚀 开始写SQL

这个题目的要求就是，交换性别，即：当sex字段是‘m’时，把它的值设置成‘’f，反之亦然，也就是说，我们必须在更新的语句中动态判断sex的值

UPDATE tb_salary 
SET sex =
CASE
		
	WHEN sex = 'm' THEN
	'f' ELSE 'm' END;

通过这个题目，我们知道，case when then 就是返回一个值，至于你用在哪里，看工作中的实际需求，比如可以是select 子句，set 子句，group by 子句和 order by 子句中

🚀 我们应该学到什么

case 的后边可以出现什么
then的后边可以执行哪些逻辑
整个的case when then 结构可以出现在SQL的哪些位置

🚩 后记

这篇文章我们了解了SQL中case when then 的基本用法，通过四个小例子实战，涵盖了常用的几种情况，其中做了一道简单题，一道中等难度的题，但是实际开发不是做题，SQL中的实际结构在实际开发中往往不像上边那么简单，而是和其它很多知识点穿插在一起。在《玩儿转 case when then 实战篇》中，我们将以更复杂的数据模型，结合工作中的实际业务场景，综合运用这篇文章中学到的知识，提高SQL编写的能力。