CASE语句遍历条件并在满足第一个条件时返回一个值
文档
语法
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)
