MySQL中的CASE/WHEN可以进行流程控制判断,比如:
CASE 字段 WHEN 具体值
例如:
id | name | sex | salary |
---|---|---|---|
1 | A | m | 2500 |
2 | B | f | 1500 |
3 | C | m | 5500 |
4 | D | f | 500 |
运行你所编写的查询语句之后,将会得到以下表:
id | name | sex | salary |
---|---|---|---|
1 | A | f | 2500 |
2 | B | m | 1500 |
3 | C | f | 5500 |
4 | D | m | 500 |
可以这样:
UPDATE salary SET sex = CASE sex
WHEN 'm' THEN 'f'
WHEN 'f' THEN 'm'
END;
也可以进行范围的判断:
CASE WHEN column BETWEEN … AND …THEN …
mysql> select *,case
-> when Salary between 65000 and 100000 then 'high'
-> when Salary between 30000 and 65000 then 'low'
-> end as 'sal'
-> from Employee;
+------+-------+--------+--------------+------+
| Id | Name | Salary | DepartmentId | sal |
+------+-------+--------+--------------+------+
| 1 | Joe | 70000 | 1 | high |
| 2 | Henry | 80000 | 2 | high |
| 3 | Sam | 60000 | 2 | low |
| 4 | Max | 90000 | 1 | high |
+------+-------+--------+--------------+------+
4 rows in set (0.00 sec)