SQL：CASE WHEN THEN ELSE END语句

语句"/>

SQL：CASE WHEN THEN ELSE END语句

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

文档

• .html

语法

CASEWHEN condition1 THEN result1WHEN condition2 THEN result2WHEN conditionN THEN resultNELSE 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, (casewhen 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)