case 表达式 & case 语句
假如我们有一张表名为 employees ，如下所示：
+-------------+------------+-----------+---------------+---------+------------+--------+
| employee_id | first_name | last_name | department_id | salary | hire_date | gender |
+-------------+------------+-----------+---------------+---------+------------+--------+
| 1 | John | Doe | 1 | 4500.00 | 2020-01-15 | M |
| 2 | Jane | Smith | 2 | 5500.00 | 2019-03-22 | F |
| 3 | Robert | Brown | 1 | 3500.00 | 2018-07-11 | M |
| 4 | Emily | Davis | 3 | 2500.00 | 2021-05-17 | F |
| 5 | Michael | Wilson | 2 | 7000.00 | 2017-11-05 | M |
| 6 | Linda | Taylor | 3 | 4200.00 | 2020-10-25 | F |
| 7 | David | Anderson | 1 | 4800.00 | 2019-08-19 | M |
| 8 | Sarah | Thomas | 2 | 5100.00 | 2018-12-13 | F |
| 9 | James | Jackson | 3 | 3100.00 | 2016-09-03 | M |
| 10 | Patricia | White | 1 | 6000.00 | 2015-02-22 | F |
+-------------+------------+-----------+---------------+---------+------------+--------+
case 表达式
CASE 表达式是用于在 SELECT 或其他查询语句中根据条件产生不同结果的方法。case 表达式有简单 case 表达式和搜索 case 表达式。（了解即可，实际上输出的结果是相同的）
基于员工的薪水（salary）字段返回不同的级别：
-- 搜索 case 表达式
SELECT first_name, last_name,
CASE
WHEN salary < 3000 THEN 'Low'
WHEN salary >= 3000 AND salary < 5000 THEN 'Medium'
WHEN salary >= 5000 THEN 'High'
ELSE 'Unknown'
END AS salary_level
FROM employees;
+------------+-----------+--------------+
| first_name | last_name | salary_level |
+------------+-----------+--------------+
| John | Doe | Medium |
| Jane | Smith | High |
| Robert | Brown | Medium |
| Emily | Davis | Low |
| Michael | Wilson | High |
| Linda | Taylor | Medium |
| David | Anderson | Medium |
| Sarah | Thomas | High |
| James | Jackson | Medium |
| Patricia | White | High |
+------------+-----------+--------------+
-- 简单 case 表达式
SELECT
first_name,
last_name,
CASE department_id
WHEN 1 THEN 'HR'
WHEN 2 THEN 'Engineering'
WHEN 3 THEN 'Sales'
ELSE 'Unknown'
END AS department_name
FROM employees;
+------------+-----------+-----------------+
| first_name | last_name | department_name |
+------------+-----------+-----------------+
| John | Doe | HR |
| Jane | Smith | Engineering |
| Robert | Brown | HR |
| Emily | Davis | Sales |
| Michael | Wilson | Engineering |
| Linda | Taylor | Sales |
| David | Anderson | HR |
| Sarah | Thomas | Engineering |
| James | Jackson | Sales |
| Patricia | White | HR |
+------------+-----------+-----------------+
10 rows in set (0.00 sec)
注意点：
- else 子句可以不用写，但是不建议。养成良好的习惯。
- end 是必不可少的。
case 语句
CASE 语句是一种更通用的形式，可以在 SQL 中的任何位置使用，例如 SELECT、UPDATE、DELETE 语句中。
- 与聚合函数结合使用。
统计每个部门的男性和女性员工数量：
SELECT
department_id,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department_id;
+---------------+------------+--------------+
| department_id | male_count | female_count |
+---------------+------------+--------------+
| 1 | 3 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
+---------------+------------+--------------+
3 rows in set (0.01 sec)
case 语句不仅能写在聚合函数里面，还能写在聚合函数外面。
统计大部门和小部门：
SELECT department_id,
CASE
WHEN COUNT(*) > 3 THEN 'big'
ELSE 'small'
END AS scale
FROM employees
GROUP BY department_id;
+---------------+-------+
| department_id | scale |
+---------------+-------+
| 1 | big |
| 2 | small |
| 3 | small |
+---------------+-------+
3 rows in set (0.00 sec)
- 在 UPDATE 语句里进行条件分支
假如让工资大于等于 5000 的降薪 20%，小于 5000 的涨薪 20%。你会怎么做？
首先最先想到的应该是两个
UPDATE进行更新。但是有没有想过执行完第一个条件后，会对第二个进行干扰，从而出现错误的结果。
下面来看一个简单的方式来完成这个要求：
UPDATE employees
SET salary =
CASE
WHEN salary >= 5000 THEN salary * 0.8
WHEN salary < 5000 THEN salary * 1.2
END;
-- 查询后的结果
+-------------+------------+-----------+---------------+---------+------------+--------+
| employee_id | first_name | last_name | department_id | salary | hire_date | gender |
+-------------+------------+-----------+---------------+---------+------------+--------+
| 1 | John | Doe | 1 | 5400.00 | 2020-01-15 | M |
| 2 | Jane | Smith | 2 | 4400.00 | 2019-03-22 | F |
| 3 | Robert | Brown | 1 | 4200.00 | 2018-07-11 | M |
| 4 | Emily | Davis | 3 | 3000.00 | 2021-05-17 | F |
| 5 | Michael | Wilson | 2 | 5600.00 | 2017-11-05 | M |
| 6 | Linda | Taylor | 3 | 5040.00 | 2020-10-25 | F |
| 7 | David | Anderson | 1 | 5760.00 | 2019-08-19 | M |
| 8 | Sarah | Thomas | 2 | 4080.00 | 2018-12-13 | F |
| 9 | James | Jackson | 3 | 3720.00 | 2016-09-03 | M |
| 10 | Patricia | White | 1 | 4800.00 | 2015-02-22 | F |
+-------------+------------+-----------+---------------+---------+------------+--------+
10 rows in set (0.00 sec)
这样看来就是很合理了。
case的用法不仅仅有这些，在case表达式里，我们可以使用BETWEEN,LIKE等等便利的谓词，以及能嵌套子查询的IN和EXISTS。