[Mysql] CASE表达式

已于 2023-05-23 21:02:21 修改
阅读量5.3k 收藏 26
点赞数 9
分类专栏： Mysql 文章标签： mysql sql
于 2022-04-01 23:12:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hudas/article/details/123901676
版权

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  地区名;

结果展示:

山茶花开时。
关注
专栏目录
MySQL--case表达式
或是深海的博客
10-13 949
本文根据《SQL进阶教程》（[日]MICK 吴炎昌/译）所写笔记。 case 表达式概述 我们用case来表达条件分支。case表达式有简单case表达式和搜索case表达式。表达式写法如下： -- 简单case表达式 case sex when '1' then '男' when '2' then '女' else '其他' end -- 搜索case表达式 case whe...
MySQL CASE表达式
Spring , Hadoop, Spark , BI , ML
07-04 1573
MySQL CASE表达式是一个流程控制结构，用在在SELECT、WHERE等语句中根据条件动态构造内容。2种MySQL CASE格式MySQL的CASE表达式有2中形式，一种更像是编程语言当中的CASE语句，拿一个给定的值（变量）跟一系列特定的值作比较,我称之为CASE类型。另一种则更像是编程语言中的if语句，当满足某些条件的时候取特定值，我称之为IF类型。CASE类型此类型的语句结构如下：CAS
Mysql表达式 | CASE IF 判空
devops
07-03 3928
MySQL 中的条件表达式用于根据条件的结果选择不同的值或执行不同的操作。条件表达式可以在 SELECT 语句中生成计算字段、在 WHERE 语句中过滤数据、在 ORDER BY 语句中排序等多种情况下使用。
滚雪球学MySQL[3.3讲]：MySQL复杂查询详解：CASE语句、自连接与视图管理
最新发布
**My Coding Family**
10-01 1251
在上一期的学习中，我们详细讲解了MySQL中的聚合函数与分组操作（3.2），包括使用COUNTSUMAVG等聚合函数对数据进行统计，并通过GROUP BY进行分组，结合HAVING进一步对聚合后的结果进行筛选。这些操作是数据统计与分析的重要工具，尤其在处理大规模数据时，能够帮助我们轻松生成有用的报告和结果。本期内容将带你进入MySQL的复杂查询（3.3）领域。相比基本的SELECT语句，复杂查询可以在更灵活的场景中应用，处理更复杂的业务逻辑和数据需求。我们将深入探讨CASE。
MySQL中的case表达式
GaGa的博客
06-23 610
case表达式和case语句的灵活使用
MySQL 入门：Case 语句很好用
宇宙之一粟的技术漂泊之旅
05-11 3万+
引言 MySQL CASE 是一个 MySQL 语句查询关键字，它定义了处理循环概念以执行条件集并使用 IF ELSE 返回匹配案例的方式。 MySQL 中的 CASE 是一种控制语句，它验证条件案例集，并在第一个案例满足 else 值时显示值并退出循环。 如果没有找到 TRUE 且语句没有 ELSE 部分或值，则 CASE 返回 NULL。基本上，CASE 语句类似于 IF THEN ELSE 逻辑循环语句。在 MySQL 服务器上运行时，它会读取条件表达式，当表达式大小写匹配时，nit 会显示结果。
SQL：CASE表达式
ISWW
04-21 742
。。。
SQL中的 CASE 表达式用法
傲慢的沙扬娜拉
04-01 1443
** SQL中的 CASE 表达式妙用这么多，你知道吗？ ** 前言 历史考试选择题：黄花岗起义第一枪谁开的? A宋教仁 B孙中山 C黄兴 D徐锡麟，考生选C。 又看第二题：黄花岗起义第二枪谁开的? 考生傻了，就选了个B。 接着看第三题：黄花岗起义中，第三枪谁开的? 考生疯了，胡乱选了A。 考试出来就去找出卷老师。老师拿出课本说：黄兴连开三枪，揭开了黄花岗起义的序幕。考生：… CASE表达式 之概念 相信大家都用过CASE表达式，尤其是做一些统计功能的时候，用的特别多，可真要说什么是 CASE表达式，我估计
mysql case when group by 实例详解
09-09
MySQL中的`CASE WHEN`语句是一种条件判断表达式，它在SQL查询中起到了类似编程语言中`switch-case`或`if-else`的作用。这个功能强大的工具允许你在单个查询中根据不同的条件返回不同的结果值，非常适合数据分析和...
mysql case后面表达式
12-13
下面是一个MySQL搜索CASE表达式的例子： ```mysql SELECT column1, column2, CASE WHEN column3 = 'value1' THEN 'result1' WHEN column3 = 'value2' THEN 'result2' ELSE 'result3' END AS result FROM table1;...
深入理解 MySQL 中的 CASE 语句：从基础到实战
一勺菠萝丶的博客
03-13 3832
CASE 语句相当于编程中的if-else或switch语句。它根据条件表达式的值选择不同的执行路径。MySQL 中的 CASE 语句是一个强大的工具，能够让我们根据条件动态地控制 SQL 查询的行为。无论是进行数据分类、动态排序还是复杂的条件计算，CASE 语句都能派上用场，让我们的查询更加灵活和强大。希望通过这些例子，你能更好地理解和使用 CASE 语句，提升你的 SQL 技能。
MySQL CASE 表达式
bt_xxx的博客
06-25 509
作为表达式，在执行时会被判断为一个固定值，因此不仅可以在SELECT子句中使用，还可以在WHERE子句、GROUP BY 子句、ORDER BY 子句中使用，它甚至可以写在聚合函数内部。如：每个人可以参加一个或多个俱乐部，当某人参加多个俱乐部时，需要用flag=1设置主俱乐部，默认flag=0。如：工资20000以上员工降薪10%；工资10000以下员工加薪20%如：已有全国各市区人口数，统计各省总人口数。如：按姓名和学科汇总成绩。
python分支if+循环（while+for）+pass
qq_42398447的博客
09-21 728
python分支+循环 if判断语句 if age>=18: print(“你已经成年，这里得缩进表示在if语句之内，如果有多条语句缩进需要对齐”) else: 缩进后加语句 print（“if语句已经结束，这句是主干语句，没有缩进”） else if 去做判断直接写成elif 判断语句: 循环 while while 条件： 条件满足时执行得代码块 while于else连用语法： 对于whlie循环中break语句，直接强制打断循环退出，else中得语句都不会执行 for 如果直接是数字次数则
mysql 函数 case_MySQL case函数的详细用法
热门推荐
weixin_33329305的博客
01-25 5万+
case 函数MySQL 的 case 函数的语法有种：1.简单函数CASE [column_name] WHEN [value1] THEN [result1]... ELSE [default] END多表查询的时候需要注意下。CASE hero_nameWHEN '盖伦' THEN '上单'WHEN '拉克丝' THEN '中单'WHEN '金克斯' THEN 'ADC'WHEN '琴女' ...
MySQL中CASE表达式的使用
TheTsing的博客
04-18 538
在 MySQL 中，CASE 表达式相当于程序设计中的一个条件判断的函数，是 SQL 语言分情况讨论的一种表达形式。 CASE 表达式有两种：一种是类似编程语言的 SWITCH CASE 。 SQL 中称为简单 CASE 表达式； 一种是类似于 IF 语句的根据条件表达式进行判断。 SQL 语言称为搜索 CASE 表达式。
MySQL函数——case函数
小伟的博客
10-15 2858
CASE函数 是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。 可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。 CASE 测试表达式 WHEN 简单表达式1 THEN 结果表达式1 WHEN 简单表达式2 THEN 结果表达式2 WHEN 简单表达式n THEN 结果表达式n ELSE 结果表达式n+1 END 按从上到下的书写顺序计算每个WHEN子句的布尔表达式。 返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。 如果没有取值为TRUE的布尔表
mysql学习—CASE表达式
weixin_43387060的博客
01-08 517
1-1 CASE表达式 CASE表达式概述 注意事项： （1）统一各分支返回的数据类型 （2）不要忘了写END （3）养成写ELSE子句的习惯 /* 把县编号转换成地区编号（1） */ SELECT CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '...
mysql 中case 用法
qq_36763236的博客
08-30 8244
1. SELECT * , CASE WHEN CLS = '0' THEN '特约商户' WHEN CLS = '1' THEN '城市应用' WHEN CLS = '2' THEN '服务网点' END typeDescriTion FROM branch_type WHERE DEL_FLAG = '0' 总结 :会...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值