CASE 具备两种格式:
简单 CASE 函数将某个表达式与一组简单表达式进行比较以肯定结果。CASE 搜索函数计算一组布尔表达式以肯定结果。 两种格式都支持可选的 ELSE 参数。web
(1)简单 CASE 函数:
CASE input_expression
WHEN when_expression THEN
result_expression […n ] [
ELSE
else_result_expression
END数据库
“举栗子”
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其余’
ENDexpress
(2)搜索 CASE 函数:
CASE
WHEN Boolean_expression THEN
result_expression […n ] [
ELSE
else_result_expression
ENDapp
“举栗子”
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其余’
ENDsvg
举栗子:
SELECT
a.name
,
a.birth_date
,
IF(a.sex
= 1, ‘女’, ‘男’) sex ,
CASE WHEN a.edu_degree
= 1 THEN ‘高中如下’
WHEN a.edu_degree
= 2 THEN ‘高中’
WHEN a.edu_degree
= 3 THEN ‘专科’
WHEN a.edu_degree
= 4 THEN ‘本科’
WHEN a.edu_degree
= 5 THEN ‘硕士’
WHEN a.edu_degree
= 6 THEN ‘博士’
END eduDegree,
b.areaname
,
c.areaname
,
d.areaname
FROM
app_user a,
areas b,
areas c,
areas d
WHERE a.address_area
= b.id
AND a.address_street
= c.id
AND a.address_village
= d.id
函数
总结:这两种方式,能够实现相同的功能。简单Case函数的写法相对比较简洁,可是和Case搜索函数相比,功能方面会有些限制,好比写判断式。还有一个须要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。code