一、序言
本文给大家介绍一些 MySQL 的高级技巧，帮助大家提高开发效率。
二、CASE WHEN 表达式
在 MySQL 中，CASE WHEN 表达式是一个灵活的条件判断结构，类似于其他编程语言中的 switch 或 if-else 语句。它允许根据不同的条件执行不同的操作。
假设我们有一个名为 orders 的表，它包含了订单的信息，包括 order_id 和 total_amount 两个字段。
| order_id (INT) | total_amount (DECIMAL) |
|---|---|
| 1 | 25.50 |
| 2 | 75.00 |
| 3 | 110.25 |
| 4 | 40.75 |
| 5 | 95.20 |
需求：根据订单的总金额，为每个订单打上了不同的标签：‘Low’, ‘Medium’, ‘High’，或者 ‘Unknown’
SELECT
order_id,
total_amount,
CASE
WHEN total_amount < 50 THEN 'Low'
WHEN total_amount >= 50 AND total_amount < 100 THEN 'Medium'
WHEN total_amount >= 100 THEN 'High'
ELSE 'Unknown'
END AS label
FROM
orders;
三、UNION
在 MySQL 中，UNION 是用于合并两个或多个 SELECT 语句的操作符。它将两个或多个查询结果集中的行合并成一个结果集，并自动去除重复的行。例如：
SELECT id, name FROM employees1
UNION
SELECT id, name FROM employees2;
以上查询会返回合并后的结果集，其中包含了 employees1 和 employees2 表中所有员工的信息。如果某个员工在两张表中都存在，UNION 会去除重复的行。如果想要保留重复的行，可以使用 UNION ALL。
SELECT id, name FROM employees1
UNION ALL
SELECT id, name FROM employees2;
四、行列转换
4.1 行转列
假设有以下 student_scores 表格：
| student_id | subject | score |
|---|---|---|
| 1 | Math | 85 |
| 1 | Science | 90 |
| 2 | Math | 75 |
| 2 | Science | 80 |
现在我们有一个需求：将每个学生的数学和科学成绩放在同一行
SELECT
student_id,
MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score
FROM
student_scores
GROUP BY
student_id;
输出结果：
| student_id | math_score | science_score |
|---|---|---|
| 1 | 85 | 90 |
| 2 | 75 | 80 |
4.2 列转行
假设有以下 sales 表格：
| date | product_A | product_B |
|---|---|---|
| 2024-01-01 | 100 | 120 |
| 2024-01-02 | 150 | 130 |
假设一个场景：我们想要将产品销售额合并到一列中，并包含产品名称和销售额的日期。可以使用列转行操作：
SELECT
date,
'product_A' AS product,
product_A AS sales
FROM
sales
UNION ALL
SELECT
date,
'product_B' AS product,
product_B AS sales
FROM
sales;
输出结果：
| date | product | sales |
|---|---|---|
| 2024-01-01 | product_A | 100 |
| 2024-01-01 | product_B | 120 |
| 2024-01-02 | product_A | 150 |
| 2024-01-02 | product_B | 130 |
五、LIMIT 提升效率
现假设一个场景：我们有一个 students 表格，想要检查是否存在年龄小于 18 岁的学生
通常我们的写法如下：
SELECT COUNT(1)
FROM students
WHERE age < 18;
上面这种写法是比较耗费性能的，我们可以使用 LIMIT 1 来限制结果集返回一行，然后检查是否存在符合条件的行。
SELECT 1
FROM students
WHERE age < 18
LIMIT 1;
如果存在至少一个年龄小于 18 岁的学生，这个查询将返回一行结果，否则不会返回任何结果。因为我们使用了 LIMIT 1，所以当找到第一个符合条件的行后，查询就会停止，并且不会继续搜索更多的匹配行，这样可以提高效率。
六、WITH AS
假设有两个表：students 表和 grades 表。students 表存储学生信息，包括学生ID（student_id）和姓名（student_name）。grades 表存储学生的成绩，包括学生ID（student_id）和成绩（grade）。
students 表：
| student_id | student_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
grades 表：
| grade_id | student_id | grade |
|---|---|---|
| 1 | 1 | 85.0 |
| 2 | 1 | 90.0 |
| 3 | 2 | 88.0 |
| 4 | 2 | 92.0 |
| 5 | 3 | 75.0 |
| 6 | 3 | 80.0 |
需求：计算每个学生的平均成绩
WITH student_avg_grades AS (
SELECT student_id, AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
)
SELECT s.student_id, s.student_name, g.avg_grade
FROM students s
JOIN student_avg_grades g ON s.student_id = g.student_id
ORDER BY g.avg_grade DESC;
输出结果：
| student_id | student_name | avg_grade |
|---|---|---|
| 2 | Bob | 90.0 |
| 1 | Alice | 87.5 |
| 3 | Charlie | 77.5 |
七、窗口函数
6.1 ROW_NUMBER()
假设我们有一个名为 employees 的表，其中包含员工信息，包括员工姓名和工资。
| employee_name | salary |
|---|---|
| Alice | 5000.00 |
| Bob | 5500.00 |
| Charlie | 5500.00 |
| David | 6500.00 |
| Eva | 5500.00 |
需求：查询每个员工及其工资排名
SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM
employees;
输出结果：
| employee_name | salary | row_number |
|---|---|---|
| David | 6500.00 | 1 |
| Bob | 5500.00 | 2 |
| Charlie | 5500.00 | 3 |
| Eva | 5500.00 | 4 |
| Alice | 5000.00 | 5 |
ROW_NUMBER() 为结果集中的每一行分配一个唯一的整数值，不会考虑并列排名。即使有相同的排序值，每一行的排名也是唯一的。如果有多个行具有相同的排序条件，则 ROW_NUMBER() 会为每个行分配不同的排名值，不会合并排名。
6.2 DENSE_RANK()
假设有一个名为 students 的表，其中包含学生的成绩信息，包括学生姓名和考试成绩。
| student_name | score |
|---|---|
| Alice | 85.50 |
| Bob | 78.00 |
| Charlie | 92.75 |
| David | 85.50 |
| Eva | 78.00 |
需求：查询每个学生的成绩及其排名（分数相同，排名相同）
SELECT
student_name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM
students;
输出结果：
| student_name | score | dense_rank |
|---|---|---|
| Charlie | 92.75 | 1 |
| Alice | 85.50 | 2 |
| David | 85.50 | 2 |
| Bob | 78.00 | 3 |
| Eva | 78.00 | 3 |
DENSE_RANK() 排名如果有多个行具有相同的排序值，则它们会被分配相同的排名，并且下一个排名会紧随其后，不会留下空隙。例如，如果有两行并列排名第 2，下一个排名仍然是第 3，不会跳过任何排名。
6.3 RANK()
假设有一个名为 sales 的表，其中包含销售人员的销售额信息。
| salesperson | sales |
|---|---|
| Alice | 15000.00 |
| Bob | 12000.00 |
| Charlie | 18000.00 |
| David | 15000.00 |
| Eva | 12000.00 |
需求：每个销售人员的销售额排名
SELECT
salesperson,
sales,
RANK() OVER (ORDER BY sales DESC) AS rank
FROM
sales;
输出结果：
| salesperson | sales | rank |
|---|---|---|
| Charlie | 18000.00 | 1 |
| Alice | 15000.00 | 2 |
| David | 15000.00 | 2 |
| Bob | 12000.00 | 4 |
| Eva | 12000.00 | 4 |
RANK() 为结果集中的每一行分配一个排名，如果有相同的排序值，则它们会被分配相同的排名，但下一个排名会留下空隙。也就是说，如果有并列排名，则下一个排名会跳过相应的排名数量。例如，如果有两行并列排名第二，下一个排名会是第 4，而不是第 3。
往期推荐
- JDK 动态代理
- ThreadLocal
- HashMap 源码分析（三）
- Spring 三级缓存
- RBAC 权限设计（二）
