1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 语法一：
case when cond1 then res1 [when cond2 then res2] else res end ;
-- 语法二(适用于等值匹配)：
case expr when val1 then res1 [when val2 then res2] else res end ;


-- 语法一示例：
select
    ( case when job=1 then '班主任'
        when job=2 then '讲师'
        when job=3 then '学工主管'
        when job=4 then '教研主管'
        when job=5 then '咨询师'
        else '其他' end) pos,
    count(*) num
from emp group by job order by num;

-- 语法二示例：
select
    ( case job when 1 then '班主任'
        when 2 then '讲师'
        when 3 then '学工主管'
        when 4 then '教研主管'
        when 5 then '咨询师'
        else '其他' end) pos,
    count(*) num
from emp group by job order by num;

-- 两种方式查询结果一致

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

if(expr,val1,val2)：如果表达式expr成立，取val1，否则取val2;
-- 示例
select if(gender = 1, '男性员工', '女性员工') name,
       count(*) value
from emp group by gender;


ifnull(expr,val1)：如果expr不为null，取自身，否则取val1;
-- 示例
select ifnull(gender, 1) from emp;