SQL：CASE WHEN THEN ELSE END语句

CASE语句遍历条件并在满足第一个条件时返回一个值

文档

• https://www.w3schools.cn/sql/sql_case.html

语法

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

示例

-- 创建用户表
create table tb_user (
    id int primary key auto_increment comment '自增主键',
    name varchar(20) not null comment '姓名',
    age int comment '年龄'
) comment '用户表';


-- 初始化数据
insert into tb_user (id, name, age) values (1, '张飞', 20);
insert into tb_user (id, name, age) values (2, '赵云', 30);
insert into tb_user (id, name, age) values (3, '刘备', 40);
insert into tb_user (id, name, age) values (4, '关羽', 25);
insert into tb_user (id, name, age) values (5, '孙权', 35);


-- 查看数据
mysql> select * from tb_user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张飞   |   20 |
|  2 | 赵云   |   30 |
|  3 | 刘备   |   40 |
|  4 | 关羽   |   25 |
|  5 | 孙权   |   35 |
+----+--------+------+
5 rows in set (0.00 sec)

查询语句中使用case

select 
    id, name, age, (
    case
        when age <= 30 then '小于等于30岁'
        when age <= 35 then '小于等于35岁'
        else '其他年龄段'
    end ) as age_case
from tb_user;

+----+--------+------+-------------------+
| id | name   | age  | age_case          |
+----+--------+------+-------------------+
|  1 | 张飞   |   20 | 小于等于30岁      |
|  2 | 赵云   |   30 | 小于等于30岁      |
|  3 | 刘备   |   40 | 其他年龄段        |
|  4 | 关羽   |   25 | 小于等于30岁      |
|  5 | 孙权   |   35 | 小于等于35岁      |
+----+--------+------+-------------------+
5 rows in set (0.00 sec)