介绍mysql数据库中case when语句的用法,首先介绍case when语句的基础知识,然后提供了相关例子。比较全面的掌握case when的用法,case when在平时的开发过程中还是使用得比较多的。
case when概述
CASE WHEN、IF、IFNULL都是MySQL中的流程控制函数。
1.CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END
2.CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
在第一个方案的返回结果中, value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为NULL。
一个CASE表达式的默认返回值类型是任何返回值的相容集合类型,但具体情况视其所在语境而定。如果用在字符串语境中,则返回结果味字符串。如果用在数字语境中,则返回结果为十进制值、实值或整数值。
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
如果expr2 或expr3中只有一个明确是 NULL,则IF() 函数的结果类型 为非NULL表达式的结果类型。
expr1 作为一个整数值进行计算,就是说,假如你正在验证浮点值或字符串值, 那么应该使用比较运算进行检验。
IF() (这一点在其被储存到临时表时很重要 ) 的默认返回值类型按照以下方式计算:
表达式 | 返回值 |
---|---|
expr2 或expr3 返回值为一个字符串。 | 字符串 |
expr2 或expr3 返回值为一个浮点值。 | 浮点值 |
expr2 或 expr3 返回值为一个整数。 | 整数 |
IFNULL(expr1,expr2)
假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。
case when语法
case when语句,用于计算条件列表并返回多个可能结果表达式之一。CASE 具有两种格式:简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。
简单CASE函数
CASE input_expression
WHEN when_expression THEN
result_expression [...n ] [
ELSE
else_result_expression
END
参数介绍:
- input_expression 是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft SQL Server 表达式。
- WHEN when_expression 使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同,或者是隐性转换。
- 占位符 ,表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。
- THEN result_expression 当 input_expression = when_expression 取值为 TRUE,或者 Boolean_expression 取值 TRUE 时返回的表达式。result expression 是任意有效的 SQL Server 表达式。
- ELSE else_result_expression 当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE,CASE 将返回 NULL 值。else_result_expression 是任意有效的 SQL Server 表达式。else_result_expression 和所有 result_expression 的数据类型必须相同,或者必须是隐性转换。
简单 CASE 函数: 返回结果值介绍:
- 计算 input_expression,然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。
- 如果没有取值为 TRUE 的 input_expression = when_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;
- 若没有指定 ELSE 子句,则返回 NULL 值。
CASE搜索函数
CASE
WHEN Boolean_expression THEN
result_expression [...n ] [
ELSE
else_result_expression
END
参数介绍:
- WHEN Boolean_expression 使用 CASE 搜索格式时所计算的布尔表达式。
- Boolean_expression 是任意有效的布尔表达式。结果类型从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。
- 有关更多信息,请参见数据类型的优先顺序。
CASE 搜索函数:返回结果值介绍:
-
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。
-
如果没有取值为 TRUE 的 Boolean_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
case when使用场景
等值转换
在设计数据库的时候总是会把用户的性别用int存储('0’为女,'1’为男),但是怎么把它转换成汉字显示呢?如果性别在配置表中进行了配置,可以两张表关联查询得到。获得在代码中进行if判断,进行转换。那还有没有其他比较好的办法,case when就可以。
简单CASE函数用例
原始表数据
sql语句
SELECT ID,
NAME AS '名字',
(CASE sex WHEN 0 THEN '女' ELSE '男' END) AS '性别'
FROM wh.user;
查询结果
范围转换
有的时候,也会遇到这种情况,按照用户成绩显示优(90+)、良(80-90)、及格(60-80)、未及格(60-),这个跟第一个不同的是,他是一个分数的范围,要怎么转换成汉字显示呢?你可能觉得很简单,不就是吧when那换成条件吗?先打住咱们往下看
CASE搜索函数用例
原始表数据
SQL语句
SELECT ID,
NAME AS '姓名'
,(CASE score WHEN score>=90 THEN '优' WHEN score>=80 THEN '良'
WHEN score>=60 THEN '及格' ELSE '不及格' END) AS '等级'
FROM wh.score;
查询结果
这是为啥呢?想明白了吗?
因为 case when就像一个 switch case语句一样,如果你在case后填了东西,它会拿它跟when对比,咱们写的case后写了score,而when后面写了score>=90 ,然而,‘score’ 等于 'score>=90’吗? 显然不等于,那该怎么写呢?
SELECT ID,
NAME AS '姓名',
(CASE WHEN score>=90 THEN '优' WHEN score>=80 THEN '良'
WHEN score>=60 THEN '及格' ELSE '不及格' END) AS '等级'
FROM wh.score;
查询结果
列转行操作
还是用学生的例子吧,现在有图1学生成绩数据, 现在要怎么按图2显示出来呢?
简单CASE函数用例
图一
图二
第一步 先按照科目分开, 符合条件的设置分数,不符合的给置零。
SELECT NAME AS '姓名'
,(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文'
,(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学'
,(CASE course WHEN '英语' THEN score ELSE 0 END) AS '英语'
FROM wh.score
查询结果
然后再按照名字group by ,对分数求max。
SELECT NAME AS '姓名'
,MAX(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文'
,MAX(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学'
,MAX(CASE course WHEN '英语' THEN score ELSE 0 END) AS '英语'
FROM wh.score GROUP BY NAME;
查询结果
本文小结
本文详细介绍了when case的用法,用法和高级程序设计语言中的switch case用法类似,就是一个多重判断的意思。介绍了when case的基本的语法以及常用的3种使用场景。