LeetCode 627.变更性别

数据准备

Create table If Not Exists Salary (id int, name varchar(100), sex char(1), salary int);
Truncate table Salary;
insert into Salary (id, name, sex, salary) values ('1', 'A', 'm', '2500');
insert into Salary (id, name, sex, salary) values ('2', 'B', 'f', '1500');
insert into Salary (id, name, sex, salary) values ('3', 'C', 'm', '5500');
insert into Salary (id, name, sex, salary) values ('4', 'D', 'f', '500');

输入

输出

with t1 as (
    select id, name,
       case
           when sex='m' then sex=1
           when sex='f' then sex=0
       end
       as sex, salary
    from Salary
)
select t1.id,t1.name,
       if(sex=0,'f','m') as sex,
       t1.salary
from t1
;