CASE WHEN 基本用法

CASE的用法分为两种形式：简单CASE函数和CASE条件表达式函数。
创建表并插入若干数据

drop table if exists worker;

CREATE TABLE worker(
	id int primary key auto_increment,
	name varchar(10) not null,
	gender char(1) not null,
	education varchar(20) not null
);
insert into worker(name, gender, education) values('张三', '男', '大专');
insert into worker values(null, '李四', '男', '本科');
insert into worker values(null, '王五', '男', '硕士');
insert into worker values(null, '赵六', '男', '博士');
select * from worker;

现在我们有这么一个需求，我们想增加一个字段，目的是为了给员工的education来评级。（简单CASE函数）

select
	name,
	gender,
	education,
	(case education
		when '大专' then '4'
		when '本科' then '3'
		when '硕士' then '2'
		when '博士' then '1'
		else '其他' end) as level
	from worker

（CASE条件表达式函数）

select
	id,
	name,
	gender,
	(case
		when gender = '男' then 'male'
		when gender = '女' then 'female'
	end) AS sex,
	education
	from worker