使用 `WITH` 子句优化复杂 SQL 查询

在 SQL 中，处理复杂的查询需求时，代码往往会变得冗长且难以维护。为了解决这个问题，SQL 提供了 WITH 子句（也称为公用表表达式，Common Table Expression，CTE）。WITH 子句允许我们定义临时表，将复杂的查询逻辑分解为多个简单的部分，从而提高代码的可读性和可维护性。本文将详细介绍 WITH 子句的使用方法，并通过一个实际案例展示其强大功能。

什么是 `WITH` 子句？

WITH 子句是 SQL 中用于定义临时表的一种语法结构。它允许我们在一个查询中创建多个临时表，这些临时表可以在后续的查询中被引用。WITH 子句的主要优点包括：

提高代码可读性：通过将复杂的查询逻辑分解为多个简单的部分，代码更易于理解和维护。
避免重复代码：临时表可以在查询中多次引用，避免重复编写相同的子查询。
支持递归查询：WITH 子句还支持递归查询，可以用于处理层次结构数据（如树形结构）。

WITH 子句定义的临时表仅在当前查询中有效，查询结束后会自动销毁，因此不会对数据库产生持久性影响。

`WITH` 子句的基本语法

WITH 子句的基本语法如下：

WITH 临时表名 AS (
    SELECT 查询语句
)
SELECT 查询语句;

临时表名：为临时表定义的名称。
SELECT 查询语句：定义临时表的具体查询逻辑。
在 WITH 子句之后，可以使用定义的临时表进行进一步的查询。

实际案例：学生成绩查询系统

假设我们有一个学生成绩管理系统，包含以下四张表：

tb_student：学生信息表，包含学生的 id、name 和 class_id。
tb_class：班级信息表，包含班级的 id 和 class_name。
tb_teacher：教师信息表，包含教师的 id 和 name。
tb_subject：课程信息表，包含课程的 id、name 和 teacher_id。
tb_score：成绩信息表，包含学生的 stu_id、课程的 subject_id 和成绩 score。

我们的目标是查询某个学生（例如姓名为 AAA 的学生）的所有成绩信息，包括学生姓名、班级名称、课程名称和成绩。

1. 定义临时表 `temp`

首先，我们定义一个临时表 temp，用于查询学生的基本信息和班级名称：

WITH temp AS (
    SELECT t.*, c.class_name 
    FROM tb_student t 
    LEFT JOIN tb_class c ON t.class_id = c.id 
)

通过 LEFT JOIN 将 tb_student 表和 tb_class 表关联，获取学生的班级名称。
temp 表包含学生的所有信息以及他们所在班级的名称。

2. 定义临时表 `temp2`

接下来，我们定义第二个临时表 temp2，用于查询教师教授的课程信息：

temp2 AS (
    SELECT t.*, s.name AS subject_name, s.id AS subject_id 
    FROM tb_teacher t 
    LEFT JOIN tb_subject s ON t.id = s.teacher_id  
)

通过 LEFT JOIN 将 tb_teacher 表和 tb_subject 表关联，获取教师教授的课程名称和课程 ID。
temp2 表包含教师的所有信息以及他们所教授的课程名称和课程 ID。

3. 使用临时表进行最终查询

最后，我们使用 temp 和 temp2 临时表进行最终查询，获取学生的成绩信息：

SELECT DISTINCT t.* 
FROM temp t 
LEFT JOIN tb_score s ON t.id = s.stu_id 
LEFT JOIN temp2 AS k ON k.subject_id = s.subject_id 
WHERE t.name = 'AAA';

通过 LEFT JOIN 将 temp 表与 tb_score 表关联，获取学生的成绩信息。
再通过 LEFT JOIN 将 temp2 表与 tb_score 表关联，获取课程的名称。
使用 WHERE 条件筛选出姓名为 AAA 的学生。

完整 SQL 查询

将上述步骤整合在一起，完整的 SQL 查询如下：

WITH temp AS (
    SELECT t.*, c.class_name 
    FROM tb_student t 
    LEFT JOIN tb_class c ON t.class_id = c.id 
),
temp2 AS (
    SELECT t.*, s.name AS subject_name, s.id AS subject_id 
    FROM tb_teacher t 
    LEFT JOIN tb_subject s ON t.id = s.teacher_id  
)
SELECT DISTINCT t.* 
FROM temp t 
LEFT JOIN tb_score s ON t.id = s.stu_id 
LEFT JOIN temp2 AS k ON k.subject_id = s.subject_id 
WHERE t.name = 'AAA';