LeetCode 627. Swap Salary

题目

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 query and no intermediate temp table.

For example:

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

After running your query, the above salary table should have the following rows:

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

题目大意:不使用临时表的情况下,用一条更新语句交换人的性别

解题思路

对每一行数据进行判断 男->女,女->男

IF/CASE

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

UPDATE salary SET sex = (
CASE WHEN sex = 'm' THEN 'f'
ELSE 'm'
END CASE )

-- CASE 标准格式
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

MySQL 5.7 Reference Manual - 13.6.5.1 CASE Syntax

SQL 中的if else case

SQL 中的位运算

UPDATE salary SET sex = CHAR(ASCII('f') ^ ASCII('m') ^ ASCII(sex));  

位运算有些时候真的是解题利器,尤其在交换与去重的有关题目中

SQL 中也是支持位运算的

image.png