1. 直接上案例
select * from boys;
# +--+----+---+----+-------+-------------------+----+
# |id|name|sex|age |address|created_time |enum|
# +--+----+---+----+-------+-------------------+----+
# |1 |慕容皝 |男 |22 |NULL |2021-05-21 17:43:43|1 |
# |2 |慕容冲 |男 |33 |NULL |2021-05-21 17:43:49|1 |
# |3 |慕容垂 |男 |44 |NULL |2021-05-21 17:43:52|1 |
# |4 |慕容博 |男 |55 |NULL |2021-05-21 17:43:55|2 |
# |6 |慕容复 |男 |55 |NULL |2021-05-21 17:43:58|2 |
# |8 |慕容吹雪|女 |NULL|NULL |2021-05-21 17:44:01|2 |
# +--+----+---+----+-------+-------------------+----+
# if 函数语法
# IF(condition, value_if_true, value_if_false)
# IF函数根据条件的结果为true或false,返回第一个值,或第二个值
# value_if_true 也分情况时候 ,可以考虑以下版本
# if (x_IsOpenSignEnum = 1,case end,if(x_IsOpenSignEnum = 2,case end ,case end ))
# 案例
# Enum 为 1 的显示 古代, Enum 为 2 的显示近代
select enum , if(enum = 1,'古代','现代') as chaodai ,boys.name from boys;
# +----+-------+----+
# |enum|chaodai|name|
# +----+-------+----+
# |1 |古代 |慕容皝 |
# |1 |古代 |慕容冲 |
# |1 |古代 |慕容垂 |
# |2 |现代 |慕容博 |
# |2 |现代 |慕容复 |
# |2 |现代 |慕容吹雪|
# +----+-------+----+
# 当然 case when then 也可以实现
# 如下
select enum,
case enum
when 1 then '古代'
when 2 then '现代'
else '其他'
end as chaodai,
boys.name
from boys;
# +----+-------+----+
# |enum|chaodai|name|
# +----+-------+----+
# |1 |古代 |慕容皝 |
# |1 |古代 |慕容冲 |
# |1 |古代 |慕容垂 |
# |2 |现代 |慕容博 |
# |2 |现代 |慕容复 |
# |2 |现代 |慕容吹雪|
# +----+-------+----+
select enum,
case
when enum = 1 then '古代'
when enum = 2 then '现代'
else '其他'
end as chaodai,
boys.name
from boys;
# +----+-------+----+
# |enum|chaodai|name|
# +----+-------+----+
# |1 |古代 |慕容皝 |
# |1 |古代 |慕容冲 |
# |1 |古代 |慕容垂 |
# |2 |现代 |慕容博 |
# |2 |现代 |慕容复 |
# |2 |现代 |慕容吹雪|
# +----+-------+----+
# if 和 case when then else end 组合使用(适合于 二叉树的情景)
# 结构语法: if (enum = 1,case when then else end,if(enum = 2,case when then else end ,case when then else end ))
# 错误写法:
select if(
enum = 1, case sex when '男' then 'man' end,
if(enum = 2, case sex when '男' then 'man' end, case when '女' then 'woman' end)) as hh,
boys.name
from boys;
# +----+----+
# |hh |name|
# +----+----+
# |man |慕容皝 |
# |man |慕容冲 |
# |man |慕容垂 |
# |man |慕容博 |
# |man |慕容复 |
# |NULL|慕容吹雪|
# +----+----+
# 慕容吹雪 的数据没有出来
# 正确写法①
select if(
enum = 1, case sex when '男' then 'man' end,
if(enum = 2, case sex when '男' then 'man' else 'woman' end,'')) as hh,
boys.name
from boys;
# +-----+----+
# |hh |name|
# +-----+----+
# |man |慕容皝 |
# |man |慕容冲 |
# |man |慕容垂 |
# |man |慕容博 |
# |man |慕容复 |
# |woman|慕容吹雪|
# +-----+----+
# 正确写法②
select if(
enum = 1, case when sex = '男' then 'man' end,
if(enum = 2, case when sex = '男' then 'man' when sex = '女' then 'woman' end, '')) as sex2,
boys.name
from boys;
# +-----+----+
# |hh |name|
# +-----+----+
# |man |慕容皝 |
# |man |慕容冲 |
# |man |慕容垂 |
# |man |慕容博 |
# |man |慕容复 |
# |woman|慕容吹雪|
# +-----+----+
1. 需求二:(if 和 case when then else end 的复杂查询)
# 案例 二
# 需求:enum 为 1 是男 ,2 是女,然后判断 是古代人,现代人(1900 到 系统当前时间),还是 未来人
select * from boys;
# +--+------+---+----+-------+-------------------+----+
# |id|name |sex|age |address|created_time |enum|
# +--+------+---+----+-------+-------------------+----+
# |1 |慕容皝 |男 |22 |NULL |1000-05-01 17:43:43|1 |
# |2 |慕容冲 |男 |33 |NULL |1005-05-21 17:43:49|1 |
# |3 |慕容垂 |男 |44 |NULL |1101-05-21 17:43:52|1 |
# |4 |慕容博 |男 |55 |NULL |1121-05-21 17:43:55|1 |
# |6 |慕容复 |男 |55 |NULL |1190-05-21 17:43:58|1 |
# |8 |慕容吹雪 |女 |NULL|NULL |2021-05-22 17:44:01|2 |
# |9 |慕容未来人1|男 |NULL|NULL |2040-05-22 17:44:01|1 |
# |10|慕容未来人2|女 |NULL|NULL |2050-05-22 17:44:01|2 |
# |11|慕容吹雪2 |女 |NULL|NULL |1901-05-22 17:44:01|2 |
# |12|慕容复2 |男 |55 |NULL |1899-05-21 17:43:58|1 |
# +--+------+---+----+-------+-------------------+----+
# if 和 case when then else end 组合使用(适合于 二叉树的情景)
# 结构语法: if (enum = 1,case when then else end,if(enum = 2,case when then else end ,case when then else end ))
# 正确写法① DATEDIFF(NOW(),'2016-11-16 17:10:52')
# date_format(created_time, '%Y-%m-%d') > '1900-01-01'
# DATEDIFF(NOW(),'2016-11-16 17:10:52')
select DATEDIFF(created_time,'1900-01-01')from boys ;
# +-----------------------------------+
# |DATEDIFF(created_time,'1900-01-01')|
# +-----------------------------------+
# |-328598 |
# |-326752 |
# |-291689 |
# |-284384 |
# |-259182 |
# |44336 |
# |51276 |
# |54928 |
# |506 |
# |-225 |
# +-----------------------------------+
# 正确写法①
select boys.name,
enum,
if(
enum = 1, case sex when '男' then 'man' end,
if(enum = 2, case sex when '男' then 'man' else 'woman' end, '')) as sex2,
created_time as createdTime,
date_format(created_time, '%Y-%m-%d') as createdTimeStr,
if(
DATEDIFF(created_time, '1900-01-01') > 0 and
DATEDIFF(created_time, '2021-05-22') <= 0, '现代人',
case if(DATEDIFF(created_time, '2021-05-22') >0,1,-1)
when 1 then '未来人'
when -1 then '古代人'
else '未知' end) as chaodai
from boys;
# +------+----+-----+-------------------+--------------+-------+
# |name |enum|sex2 |createdTime |createdTimeStr|chaodai|
# +------+----+-----+-------------------+--------------+-------+
# |慕容皝 |1 |man |1000-05-01 17:43:43|1000-05-01 |古代人 |
# |慕容冲 |1 |man |1005-05-21 17:43:49|1005-05-21 |古代人 |
# |慕容垂 |1 |man |1101-05-21 17:43:52|1101-05-21 |古代人 |
# |慕容博 |1 |man |1121-05-21 17:43:55|1121-05-21 |古代人 |
# |慕容复 |1 |man |1190-05-21 17:43:58|1190-05-21 |古代人 |
# |慕容吹雪 |2 |woman|2021-05-22 17:44:01|2021-05-22 |现代人 |
# |慕容未来人1|1 |man |2040-05-22 17:44:01|2040-05-22 |未来人 |
# |慕容未来人2|2 |woman|2050-05-22 17:44:01|2050-05-22 |未来人 |
# |慕容吹雪2 |2 |woman|1901-05-22 17:44:01|1901-05-22 |现代人 |
# |慕容复2 |1 |man |1899-05-21 17:43:58|1899-05-21 |古代人 |
# +------+----+-----+-------------------+--------------+-------+
# 正确写法②
select boys.name,
enum,
if(
enum = 1, case sex when '男' then 'man' end,
if(enum = 2, case sex when '男' then 'man' else 'woman' end, '')) as sex2,
created_time as createdTime,
date_format(created_time, '%Y-%m-%d') as createdTimeStr,
if(
DATEDIFF(created_time, '1900-01-01') > 0 and
DATEDIFF(created_time, '2021-05-22') <= 0, '现代人',
case
when DATEDIFF(created_time, '2021-05-22') > 0 then '未来人'
when DATEDIFF(created_time, '1900-01-01') < 0 then '古代人'
else '未知' end) as chaodai
from boys;
# +------+----+-----+-------------------+--------------+-------+
# |name |enum|sex2 |createdTime |createdTimeStr|chaodai|
# +------+----+-----+-------------------+--------------+-------+
# |慕容皝 |1 |man |1000-05-01 17:43:43|1000-05-01 |古代人 |
# |慕容冲 |1 |man |1005-05-21 17:43:49|1005-05-21 |古代人 |
# |慕容垂 |1 |man |1101-05-21 17:43:52|1101-05-21 |古代人 |
# |慕容博 |1 |man |1121-05-21 17:43:55|1121-05-21 |古代人 |
# |慕容复 |1 |man |1190-05-21 17:43:58|1190-05-21 |古代人 |
# |慕容吹雪 |2 |woman|2021-05-22 17:44:01|2021-05-22 |现代人 |
# |慕容未来人1|1 |man |2040-05-22 17:44:01|2040-05-22 |未来人 |
# |慕容未来人2|2 |woman|2050-05-22 17:44:01|2050-05-22 |未来人 |
# |慕容吹雪2 |2 |woman|1901-05-22 17:44:01|1901-05-22 |现代人 |
# |慕容复2 |1 |man |1899-05-21 17:43:58|1899-05-21 |古代人 |
# +------+----+-----+-------------------+--------------+-------+
mybatis 中的写法如下:
<select id ='getList2' resultMap="BaseResultMap">
select boys.name,
if(
enum = 1, case sex when '男' then 'man' end,
if(enum = 2, case sex when '男' then 'man' else 'woman' end, '')) as sex2,
created_time as createdTime,
date_format(created_time, '%Y-%m-%d') as createdTimeStr,
if(
DATEDIFF(created_time, '1900-01-01') > 0 and
DATEDIFF(created_time, '2021-05-22') <= 0, '现代人',
case
when DATEDIFF(created_time, '2021-05-22') > 0 then '未来人'
when DATEDIFF(created_time, '1900-01-01') < 0 then '古代人'
else '未知' end) as chaodai
from boys
</select>
# 案例 三
# 需求:enum 为 1 是男 ,2 是女,然后判断 是古代人(m)| 古代人(w),现代人(1900 到 系统当前时间)(m)| 现代人(w),还是 未来人(m)|未来人(w)
select * from boys;
# +--+------+---+----+-------+-------------------+----+
# |id|name |sex|age |address|created_time |enum|
# +--+------+---+----+-------+-------------------+----+
# |1 |慕容皝 |男 |22 |NULL |1000-05-01 17:43:43|1 |
# |2 |慕容冲 |男 |33 |NULL |1005-05-21 17:43:49|1 |
# |3 |慕容垂 |男 |44 |NULL |1101-05-21 17:43:52|1 |
# |4 |慕容博 |男 |55 |NULL |1121-05-21 17:43:55|1 |
# |6 |慕容复 |男 |55 |NULL |1190-05-21 17:43:58|1 |
# |8 |慕容吹雪 |女 |NULL|NULL |2021-05-22 17:44:01|2 |
# |9 |慕容未来人1|男 |NULL|NULL |2040-05-22 17:44:01|1 |
# |10|慕容未来人2|女 |NULL|NULL |2050-05-22 17:44:01|2 |
# |11|慕容吹雪2 |女 |NULL|NULL |1901-05-22 17:44:01|2 |
# |12|慕容复2 |男 |55 |NULL |1899-05-21 17:43:58|1 |
# +--+------+---+----+-------+-------------------+----+
# if 和 case when then else end 组合使用(适合于 二叉树的情景)
# 结构语法: if (enum = 1,case when then else end,if(enum = 2,case when then else end ,case when then else end ))
# 多个 case when then 语法结构(case 和 end 是成对出现的)
case
when conditionA then
case
when condition_a
then
value_a
when condition_b
then
value_b
when condition_c
then
value_c
else
value_other
end
end
# 正确写法①
select boys.name,
created_time as createdTime,
date_format(created_time, '%Y-%m-%d') as createdTimeStr,
if(
enum = 1,
# if 函数第二个参数
case
when sex = '男' then
case
when DATEDIFF(created_time, '1900-01-01') < 0
then
'古代人(m)'
when DATEDIFF(created_time, '1900-01-01') >= 0 and DATEDIFF(created_time, '2021-05-22') <= 0
then
'现代人(m)'
when DATEDIFF(created_time, '2021-05-22') > 0
then
'未来人(m)'
else
'未知(男)'
end
end,
# if 函数第三个参数
case
when sex = '女' then
case
when DATEDIFF(created_time, '1900-01-01') < 0
then
'古代人(w)'
when DATEDIFF(created_time, '1900-01-01') >= 0 and DATEDIFF(created_time, '2021-05-22') <= 0
then
'现代人(w)'
when DATEDIFF(created_time, '2021-05-22') > 0
then
'未来人(w)'
else
'未知(女)'
end
end
# if 第三个条件结束
) as chaodaisex
from boys;
# +------+-------------------+--------------+----------+
# |name |createdTime |createdTimeStr|chaodaisex|
# +------+-------------------+--------------+----------+
# |慕容皝 |1000-05-01 17:43:43|1000-05-01 |古代人(m) |
# |慕容冲 |1005-05-21 17:43:49|1005-05-21 |古代人(m) |
# |慕容垂 |1101-05-21 17:43:52|1101-05-21 |古代人(m) |
# |慕容博 |1121-05-21 17:43:55|1121-05-21 |古代人(m) |
# |慕容复 |1190-05-21 17:43:58|1190-05-21 |古代人(m) |
# |慕容吹雪 |2021-05-22 17:44:01|2021-05-22 |现代人(w) |
# |慕容未来人1|2040-05-22 17:44:01|2040-05-22 |未来人(m) |
# |慕容未来人2|2050-05-22 17:44:01|2050-05-22 |未来人(w) |
# |慕容吹雪2 |1901-05-22 17:44:01|1901-05-22 |现代人(w) |
# |慕容复2 |1899-05-21 17:43:58|1899-05-21 |古代人(m) |
# +------+-------------------+--------------+----------+
# 正确写法② (用 case when then 嵌套)
select boys.name,
created_time as createdTime,
date_format(created_time, '%Y-%m-%d') as createdTimeStr,
case
when enum = 1 then
case
when sex = '男' then
case
when DATEDIFF(created_time, '1900-01-01') < 0
then
'古代人(m)'
when DATEDIFF(created_time, '1900-01-01') >= 0 and DATEDIFF(created_time, '2021-05-22') <= 0
then
'现代人(m)'
when DATEDIFF(created_time, '2021-05-22') > 0
then
'未来人(m)'
else
'未知(男)'
end
end
when enum = 2 then
case
when sex = '女' then
case
when DATEDIFF(created_time, '1900-01-01') < 0
then
'古代人(w)'
when DATEDIFF(created_time, '1900-01-01') >= 0 and DATEDIFF(created_time, '2021-05-22') <= 0
then
'现代人(w)'
when DATEDIFF(created_time, '2021-05-22') > 0
then
'未来人(w)'
else
'未知(女)'
end
end
else '未知'
end
as chaodaisex
from boys;
# +------+-------------------+--------------+----------+
# |name |createdTime |createdTimeStr|chaodaisex|
# +------+-------------------+--------------+----------+
# |慕容皝 |1000-05-01 17:43:43|1000-05-01 |古代人(m) |
# |慕容冲 |1005-05-21 17:43:49|1005-05-21 |古代人(m) |
# |慕容垂 |1101-05-21 17:43:52|1101-05-21 |古代人(m) |
# |慕容博 |1121-05-21 17:43:55|1121-05-21 |古代人(m) |
# |慕容复 |1190-05-21 17:43:58|1190-05-21 |古代人(m) |
# |慕容吹雪 |2021-05-22 17:44:01|2021-05-22 |现代人(w) |
# |慕容未来人1|2040-05-22 17:44:01|2040-05-22 |未来人(m) |
# |慕容未来人2|2050-05-22 17:44:01|2050-05-22 |未来人(w) |
# |慕容吹雪2 |1901-05-22 17:44:01|1901-05-22 |现代人(w) |
# |慕容复2 |1899-05-21 17:43:58|1899-05-21 |古代人(m) |
# +------+-------------------+--------------+----------+
1. 在mybatis 的用法注意:
①
// 实体类里的 xCloseReasonEnum 这个属性为 Integer 类型,then null 后结果 是null
CASE WHEN x_IsOpenSignEnum = 1 THEN null
ELSE
case
when
x_CloseReasonEnum = 0
then null else x_CloseReasonEnum end
END AS x_CloseReasonEnum
②
// 实体类里的 xCloseReasonEnum 这个属性为 Integer 类型,then '' 后结果 是 0
CASE WHEN x_IsOpenSignEnum = 1 THEN ''
ELSE
case
when
x_CloseReasonEnum = 0
then '' else x_CloseReasonEnum end
END AS x_CloseReasonEnum