case when的判断顺序_CASE 表达式

最新推荐文章于 2024-04-29 19:28:06 发布
最新推荐文章于 2024-04-29 19:28:06 发布
阅读量1.4k 收藏 3
点赞数
分类专栏： mysql 文章标签： sql
原文链接：https://blog.csdn.net/weixin_33502772/article/details/112405012
版权

一、语法及作用


使用CASE表达式可以帮助我们解决复杂的查询问题，相当于条件判断的函数，判断每一行是不是满足条件。

CASE WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     ....
     ELSE(表达式)
END;


CASE 表达式会从对最初的WHEN子句中的“< 求值表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真(TRUE)，那么就返回THEN子句中的表达式，CASE表达式的执行到此为止。如果结果不为真，那么就跳转到下一条WHEN子句的求值之中。 如果直到最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的表达式，执行终止。

二、注意事项

  1. 在发现为真的 WHEN 子句时， CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。因此，为了避免引起不必要的混乱，使用 WHEN 子句时要注意条件的排他性。
  2. CASE 表达式里各个分支返回的数据类型必须一致，如果不一致，则需使用CAST()函数转换数据类型。
  3. 虽然CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易理解，还是尽量不要省略。
  4. CASE 表达式中的 END 不能省略。
  5. 使用CASE表达式能够将SELECT语句的结果进行组合。

三、适用情况


3.1 将已有编号方式转换为新的方式并统计

例如，统计下表 PopTbl 中的内容，得出如右表“统计结果”所示的结果。


首先，可以通过定义一个包含“地区编号”列的视图来实现。但是这种方法需要添加的行的数量将等同于统计对象的编号个数，而且很难动态地修改。因此，可以使用CASE表达式来实现:

SELECT CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
            WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
            ELSE '其他'
            END AS district,
       SUM(population) AS population
FROM PopTbl
GROUP BY CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
              WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
              ELSE '其他'
              END
ORDER BY population DESC;


这里的关键在于将SELECT子句里的CASE表达式复制到 GROUP BY 子句里。后期修改时有可能只改了一处而忘了另一处。因此，可以在 GROUP BY 子句中引用 SELECT 子句中定义的别名 (如下代码所示)，这样写出来的 SQL 语句确实非常简洁，而且可读性也很好 。

SELECT CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
            WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
            ELSE '其他'
            END AS district,
       SUM(population) AS population
FROM PopTbl
GROUP BY district
ORDER BY population DESC;


严格来说，这种写法是违反标准 SQL 的规则的，因为 GROUP BY 子句比 SELECT 语句先执行。事实上，在 Oracle、DB2、 SQL Server 等数据库里采用这种写法时就会出错。 不过在 PostgreSQL 和 MySQL 中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。不过因为这是违反标准的写法，所以这里不强烈推荐大家使用。

3.2 行列转换：在聚合函数中使用CASE WHEN 表达式

3.2.1 统计下表 PopTbl2 中的内容，得出如右表“统计结果”所示的结果。


 

SELECT pref_name,
 SUM(CASE WHEN sex = 1 THEN population ELSE 0 END) AS '男性人口',
 SUM(CASE WHEN sex = 0 THEN population ELSE 0 END) AS '女性人口'
FROM PopTbl2
GROUP BY pref_name;


3.2.2 统计 PopTbl2 表中的容，得出如下表所示的结果。

 

SELECT CASE WHEN sex = 1 THEN 'male' ELSE 'female' END AS sex2,
SUM(population) AS 'total',
SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS '德岛',
SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS '香川',
SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS '爱媛',
SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS '高知',
SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN population ELSE 0 END) AS '四国'
FROM PopTbl2
GROUP BY sex2;


3.3 表之间的数据匹配：在 CASE WHEN 表达式中使用子查询


方法一：子查询 (X 和 O 表示)

SELECT course_name,
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200706')
 THEN 'O' ELSE 'X' END AS 'June',
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200707')
 THEN 'O' ELSE 'X' END AS 'July',
 CASE WHEN course_id IN 
  (SELECT course_id FROM OpenCourses WHERE month = '200708')
 THEN 'O' ELSE 'X' END AS 'August'
FROM CourseMaster;


方法二：联结 (0 和 1表示)

SELECT a.course_name,
 MAX(CASE WHEN b.month = '200706' THEN 1 ELSE 0 END) AS 'June',
 MAX(CASE WHEN b.month = '200707' THEN 1 ELSE 0 END) AS 'July',
 MAX(CASE WHEN b.month = '200708' THEN 1 ELSE 0 END) AS 'August'
FROM CourseMaster AS a
LEFT JOIN OpenCourses AS b ON a.course_id = b.course_id
GROUP BY a.course_name;


3.4 在 CASE 表达式中使用聚合函数

如表 StudentClub 所示，这张表的主键是“学号、社团 ID”，存储了学生和社团之间多对多的关系。有的学生同时加入了多个社团(如学号为 100、200 的学生)，有的学生只加入了某一个社团(如学号为 300、400、500 的学生)。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N。


接下来，我们需要查询获取只加入了一个社团的学生的社团 ID 以及获取加入了多个社团的学生的主社团 ID，并将结果存放在一个表中。

方法一：UNION ALL

SELECT std_id, MAX(club_id) AS 'main_club' FROM StudentClub
GROUP BY std_id
HAVING COUNT(club_id) = 1
UNION ALL
SELECT std_id, club_id AS 'main_club' FROM StudentClub
WHERE main_club_flg = 'Y'
ORDER BY std_id;


方法二：CASE WHEN 表达式

SELECT std_id, 
CASE WHEN COUNT(club_id) = 1 THEN MAX(club_id)
     ELSE MAX(CASE WHEN main_club_flg = 'Y' THEN club_id ELSE NULL END) 
     END AS 'main_club'
FROM StudentClub
GROUP BY std_id;


3.5 多列数据的最大值

针对每一个key_col，取出x、y、z中最大的值 (取出每一行的最大值)


方法一：自己想的

SELECT key_col,
 CASE WHEN x > y AND x > z THEN x
      WHEN y > x AND y > z THEN y
      ELSE z
 END AS 'max_col'
FROM Greatests;


方法二：穷尽讨论

SELECT key_col,
       CASE WHEN CASE WHEN x < y THEN x ELSE y END < z THEN z ELSE
       CASE WHEN x < y THEN y ELSE x END
       END AS greatest
FROM Greatests;


方法三：运用 UNION ALL 进行行列转换

SELECT key_col, MAX(col) AS greatest FROM
 (SELECT key_col, x AS col FROM Greatests
  UNION ALL
  SELECT key_col, y AS col FROM Greatests
  UNION ALL
  SELECT key_col, z AS col FROM Greatests) AS a
GROUP BY key_col;


方法四：GREATEST() 函数

SELECT key_col, GREATEST(x, y, z) AS greatest 
FROM Greatests;


GREATEST() 函数介绍：

https://www.w3schools.com/sql/func_mysql_greatest.asp​www.w3schools.com
3.6 在 ORDER BY 语句中使用 CASE WHEN 表达式

3.6.1 针对3.5中的结果，将结果按照 B-A-D-C的顺序排序

SELECT key_col,
       CASE WHEN CASE WHEN x < y THEN x ELSE y END < z THEN z ELSE
       CASE WHEN x < y THEN y ELSE x END
       END AS greatest
FROM Greatests
ORDER BY 
 (CASE WHEN key_col = 'B' THEN 1
       WHEN key_col = 'A' THEN 2
       WHEN key_col = 'D' THEN 3
       ELSE 4 END);


3.6.2 如果job是"SALESMAN",则根据"comm"排序，否则根据"sal"排序


方法1：

SELECT ename, sal, job, comm FROM emp
ORDER BY 
 CASE WHEN job = 'SALESMAN' THEN comm
 ELSE sal
 END;


方法2：

SELECT ename, sal, job, comm,
 CASE WHEN job = 'SALESMAN' THEN comm
 ELSE sal
 END AS ordered
FROM emp
ORDER BY 5;


3.7 使用 CASE WHEN 语句在 UPDATE 语句里进行条件分支：

例如：对公司工资表中(Salaries)的员工工资(salary)进行更新：对当前工资为 30 万日元以上的员工，降薪 10%；对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

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;


3.8 调换主键值：

通常，当我们想调换主键值 a 和 b 时，需要将主键值临时转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操作：

-- 1. 将 a 转换为中间值 d 
UPDATE SomeTable
SET p_key = 'd' 
WHERE p_key = 'a';
 
-- 2.将b调换为a 
UPDATE SomeTable
SET p_key = 'a'
WHERE p_key = 'b';
 
-- 3.将d调换为b 
UPDATE SomeTable
SET p_key = 'b' 
WHERE p_key = 'd';


但是，如果使用 CASE 表达式，1 次就可以做到：

UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
                 WHEN p_key = 'b' THEN 'a'
         ELSE p_key
                 END;


一般来说需要进行这样的调换大多是因为表的设计出现了问题，所以请先重新审视一下表的设计，去掉不必要的约束。

四、练习


表关系请参考如下文章 (SQL面试50题) 中的四张表

shanshant：SQL面试50题​zhuanlan.zhihu.com

练习一：查询每门课程的及格人数和不及格人数

方法一：自己写的

SELECT c_id, SUM(及格) AS 及格人数, SUM(不及格) AS 不及格人数
FROM
(SELECT c_id,
 CASE WHEN score >=60 THEN 1 
 ELSE 0 
 END AS '及格',
 CASE WHEN score <60 THEN 1 
 ELSE 0 
 END AS '不及格'
 FROM scores) AS c
GROUP BY c_id;


方法二：参考猴子老师的

SELECT c_id,
SUM(CASE WHEN score >= 60 THEN 1
    ELSE 0
    END) AS 及格人数,
SUM(CASE WHEN score < 60 THEN 1
    ELSE 0
    END) AS 不及格人数
FROM scores
GROUP BY c_id;


练习二：使用分段 [100-85], [85-70], [70-60], [<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称。

SELECT a.c_id, b.c_name,
SUM(CASE WHEN score >= 85 THEN 1
    ELSE 0
    END) AS 85至100分数段,
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1
    ELSE 0
    END) AS 70至85分数段,
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1
    ELSE 0
    END) AS 60至70分数段,
SUM(CASE WHEN score < 60 THEN 1
    ELSE 0
    END) AS 不及格
FROM scores AS a
RIGHT JOIN classes AS b
ON a.c_id = b.id
GROUP BY a.c_id, b.c_name;


相关资源：Sql_Case_When用法_casewhen执行顺序-SQLServer文档类资源-CSDN文库
 

☞空白页
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL排序中使用CASE WHEN的方法示例
12-16
前言 在之前的一个项目中，使用到了SQL中使用 CASE WHEN 排序的功能。现在写篇博客备忘~ 数据库版本：MySQL 5.6.42 条件： 某字段代表该数据的状态取值为非负整数，0表示无状态。 需求： 以该字段升序排序，同时需要将值为0的数据放在最后。 首先我们看一下，表的结构： 正常的使用升序查询结果是这样的： 要让“小赤”排在最后，可以这样写： 也可以这样： 还可以使用 CASE WHEN 再不影响查询结果的前提下，改变排序的依据： 总结 以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对软件开发网
SQL之CASE WHEN函数语句多条件下使用详解
m0_60154368的博客
05-09 9561
若是多重时，WHEN 的 IN 条件中的值和下一个 WHEN 的条件重合会被忽略，如下面的 “关闭” 状态会被忽略，永远无法得到 “关闭”这两种格式式，可以实现相同的功能。针对CASE WHEN函数语句，实现简单CASE函数和CASE搜索函数两种格式。根据时间条件查询值，有值时显示 “被求和值”， 没有值时，显示 “null”根据时间条件查询值，有值时显示 “总条数值”， 没有值时，显示 “0”同时配合 SUM以及COUNT方法的使用。简单CASE函数和CASE搜索函数。
SQL语法之CASE WHEN的使用总结
本人的技术和文笔有限，正在努力成长中，请各位朋友多多指正与包涵！
06-23 5310
如果 column1 的值为 ‘B’，则筛选 column2 小于 20 的数据；在这个语法中，CASE 表示开始一个 CASE WHEN 表达式，WHEN 表示一个条件分支，THEN 表示条件成立时返回的结果，ELSE 表示所有分支都不成立时返回的默认结果，END 表示结束整个 CASE WHEN 表达式。在这种语法中，column_name 表示要进行分类或分段处理的列名，value 表示要匹配的数值，result 表示匹配成功时返回的结果，ELSE 表示所有分支都不匹配时返回的默认结果。
CASE WHEN 条件判断
weixin_59842401的博客
02-18 319
MySQL中的CASE WHEN条件判断函数在MySQL中，CASE WHEN语句是一种非常强大的条件判断工具，它允许我们在查询中根据一系列条件返回不同的值。CASE WHEN语句类似于其他编程语言中的if-else语句或switch-case语句。本文将介绍如何在MySQL中使用CASE WHEN语句，并通过示例说明其用法。
sql case when的用法（转）
weixin_43955984的博客
09-18 3580
简单CASE WHEN函数： CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE ‘不及格’ END CASE SCORE WHEN ‘B’ THEN ‘良’ ELSE ‘不及格’ END CASE SCORE WHEN ‘C’ THEN ‘中’ ELSE ‘不及格’ END  等同于，使用CASE WHEN条件表达式函数实现： CASE WHEN SCORE = ‘A’...
SQL中的CASE WHEN语句：从基础到高级应用指南
wt334502157的博客
08-30 2722
SQL中的CASE WHEN语句是一种灵活的条件逻辑工具，用于查询中根据条件生成不同结果。它处理多种条件，可嵌套使用，适用于简单判断到复杂业务规则。无论数据转换还是业务分析，CASE WHEN都是不可或缺的工具。
【MySQL】SQL之CASE WHEN用法详解
人类所有真实的快乐，一定是恒久的努力
10-18 3万+
SQL之CASE WHEN用法详解
sql语句之case when的用法
hubolu123的博客
01-27 6万+
应用场景： 当需要从数据源上直接判断数据显示带不动含义的时候，就可以在SQL语句中使用case when 函数。 语法如下： Case具有两种格式。简单Case函数和Case搜索函数。 简单Case函数格式： CASE 列名 WHEN 条件值1 THEN 选项1 WHEN 条件值2 THEN 选项2 …… ELSE 默认值 END （注释：列名，这里是sex，如果放在case后，则显示在屏幕上的列名即为列名sex） Case搜索函数： CASE WHEN 条件1 THEN 选项1 WHEN 条件2 THE
Case when的用法
热门推荐
mry6的博客
07-10 18万+
Case when 的用法: 一旦满足了某一个WHEN, 则这一条数据就会退出CASE WHEN , 而不再考虑 其他CASE; Case when 的用法 --   搜索Case函数: Case函数(Case搜索函数): 判断表达式的真假,如果为真,返回结果;如果为假,返回else值;如果未定义else值,则返回空值(使用条件确定返回值); select name,id,(case wh...
SQL中case when用法详解及使用案例
weixin_41979002的博客
03-23 1万+
Case具有两种格式。简单Case函数和Case搜索函数。
【Mysql-3】条件判断函数-CASE WHEN、IF、IFNULL详解
云深不知处
07-19 2万+
将null转为0 在Java程序中调用sql语句时，如果返回结果是null，是非常容易引发程序抛出空指针异常的。 单字段结果由null转0 1、普通sql： // 返回结果：null SELECT price FROM goods WHERE name='light'; 2、IFNULL包含单个字段： // 返回结果：0 SELECT IFNULL(price,0) price FROM g...
Ruby中case表达式详解
01-02
Ruby的case表达式有两种形式： 第一种形式接近于一组连续的if语句：它让你列出一组条件，并执行第一个为真的条件表达式所对应的语句。 第二种形式，在case语句的顶部指定一个目标，而每个when从句列出一个或者多个...
oracle case when 语句的用法详解
12-16
1. CASE WHEN 表达式有两种形式 代码如下:–简单Case函数 CASE sex WHEN ‘1’ THEN ‘男’ WHEN ‘2’ THEN ‘女’ ELSE ‘其他’ END –Case搜索函数 CASEWHEN sex = ‘1’ THEN ‘男’ WHEN sex = ‘2’ ...
MySQL 的CASE WHEN 语句使用说明
12-15
case when语句，用于计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式： 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都...
CASE表达式实现基于条件逻辑来返回一个值
12-15
我们在开发过程中，经常需要针对一列，基于条件逻辑来返回一个值，那么，这时候就需要使用到CASE表达式了。 例如，以下对Products表的查询就在SELECT语句中使用了CASE表达式，以生成用于描述categoryid列取值的信息...
Oracle_case_when_用法
08-31
详细讲解了case、when的用法.ASE表达式可以在SQL中实现if-then-else型的逻辑，而不必使用PL/SQL。CASE的工作方式与DECODE()类似，但应该使用CASE,因为它与ANSI兼容。
SQL Server的基本操作示例
最新发布
weixin_58566539的博客
04-29 469
SQL Server的基本操作示例
攻防演练作为红方：postgresql提权之UDF提权 ，及其好用的工具
keyboard专栏
04-28 404
在 PostgreSQL 中，用户定义的函数（UDF）提权是一种常见的提升权限的方法，尤其是当你已经获得了数据库的访问权限但需要更高级别系统权限时。UDF 提权涉及在数据库中创建或使用函数，这些函数可以执行系统级别的命令，从而允许攻击者在数据库服务器上执行任意代码。
SQL底层执行过程
qq_14886109的博客
04-28 810
SQL底层执行过程，INNODB，二阶段提交
mysqlCASE WHEN表达式
07-27
MySQL的CASE WHEN表达式是一种条件语句，用于根据特定的条件执行不同的操作或返回不同的值。它类似于其他编程语言中的switch语句。 CASE WHEN语法如下： ``` CASE WHEN condition1 THEN result1 WHEN condition2 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值