[LeetCode]627. Swap Salary

发表于 | 分类于 | 评论数： | 阅读次数：
字数统计：256 字 | 阅读时长：1 分钟

627. Swap Salary

Easy

SQL Schema

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update statement and no intermediate temp table.

Note that you must write a single update statement, DO NOT write any select statement for this problem.

Example:

1
2
3
4
5
6
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

After running your

update

statement, the above salary table should have the following rows:

1
2
3
4
5
6
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

题目说明，交换sex字段的男女性别

方案一

1
UPDATE salary set sex = CASE sex WHEN 'm' THEN 'f' WHEN 'f' THEN 'm' END

result

Runtime: 193 ms, faster than 5.38% of MySQL online submissions for Swap Salary.

效率差的不是一点点。。

方案二

看看讨论区

1
2
UPDATE salary
SET sex = CHAR(ASCII('f') + ASCII('m') - ASCII(`sex`));

result

Runtime: 143 ms, faster than 72.96% of MySQL online submissions for Swap Salary.

方案三

1
UPDATE salary SET sex = IF(sex='m','f','m');

result

Runtime: 143 ms, faster than 72.96% of MySQL online submissions for Swap Salary.