介绍
在这个实验中，你将探索 SQLite 中通用表表达式（Common Table Expressions，CTEs）的强大功能。你将学习如何定义和使用 CTE，以增强查询的可读性和可维护性。你将从简单的 CTE 开始，然后转向递归 CTE。完成这个实验后，你将能够使用 CTE 编写更简洁、更高效且更易于理解的 SQL 代码。
创建数据库和表
在这一步中，你将创建一个 SQLite 数据库和一个 employees（员工）表。该表将存储员工信息，你将在后面的步骤中使用这些信息来练习 CTE 查询。
首先，在 LabEx VM 中打开你的终端。你的默认路径是 /home/labex/project。
现在，让我们创建一个名为 company.db 的 SQLite 数据库。运行以下命令来创建数据库文件并打开 SQLite 命令行工具：
sqlite3 company.db
你将看到一个提示，表明你现在位于 SQLite shell 中：
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
接下来，创建一个名为 employees 的表来存储基本的员工信息。该表将有 4 个列：id、name（姓名）、department（部门）和 salary（薪水）。在 sqlite> 提示符下输入以下 SQL 命令，然后按 Enter 键：
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);
此命令设置 employees 表，其中：
id是一个整数，用作每个员工的主键（primary key）。name是一个文本字段，用于员工的姓名。department是一个文本字段，用于员工的部门。salary是一个整数字段，用于员工的薪水。
如果命令成功运行，你将看不到任何输出。
向表中插入数据
现在你已经创建了 employees 表，让我们向其中添加一些数据。我们将向表中插入 5 条员工记录。
通过在 sqlite> 提示符下逐个运行以下命令，将 5 条员工记录插入到 employees 表中：
INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);
这些命令将 5 行添加到 employees 表中。
INSERT INTO employees (name, department, salary)指定你正在将数据插入到employees表的name、department和salary列中。VALUES ('Alice', 'Sales', 50000)提供了要为每条记录插入的值。
为了确认数据已正确添加，请运行以下命令以查看表中的所有记录：
SELECT * FROM employees;
预期输出：
1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000
此输出显示了每条记录的 id、name、department 和 salary。SELECT * 命令检索指定表中的所有列。
定义一个简单的 CTE
在这一步中，你将学习如何在 SQLite 中定义和使用一个简单的公共表表达式（Common Table Expression，CTE）。CTE 是临时的、命名的结果集，你可以在单个 SQL 语句中引用它们。它们对于将复杂的查询分解为更小、更易于管理的部分非常有用，从而提高可读性和可维护性。
CTE 本质上是一个命名的子查询，它仅在单个查询的持续时间内存在。你可以使用 WITH 子句定义 CTE，为其指定名称并指定生成结果集的查询。然后，你可以在主查询中引用 CTE 的名称，就像它是一个常规表一样。
基本语法：
WITH
cte_name AS (
SELECT column1, column2
FROM table1
WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;
让我们定义一个 CTE 来选择 'Sales'（销售）部门的员工。在 sqlite> 提示符下执行以下 SQL 语句：
WITH
SalesEmployees AS (
SELECT id, name, salary
FROM employees
WHERE department = 'Sales'
)
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;
这个查询首先定义一个名为 SalesEmployees 的 CTE，它选择 'Sales' 部门中所有员工的 id、name 和 salary。然后，主查询从 SalesEmployees CTE 中选择 id、name 和 salary，其中薪水大于 52000。
预期输出：
3|Charlie|55000
这表明 Charlie 是销售部门中唯一一个薪水大于 52000 的员工。
构建一个递归 CTE
在这一步中，你将学习如何在 SQLite 中构建和使用递归公共表表达式（Common Table Expression，CTE）。递归 CTE 用于查询分层或树状结构的数据。它们允许你遍历表中的关系，并检索不同层级的数据。
递归 CTE 是指引用自身的 CTE。它由两部分组成：
- 锚成员（Anchor Member）： 定义递归的基本情况或起点的初始
SELECT语句。 - 递归成员（Recursive Member）： 引用 CTE 自身的
SELECT语句。这部分执行递归步骤，在前一次迭代的结果之上构建。
锚成员和递归成员使用 UNION ALL 运算符组合在一起。递归持续进行，直到递归成员返回一个空的结果集。
首先，让我们创建一个名为 employees_hierarchy 的表，其结构如下：
CREATE TABLE employees_hierarchy (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER,
title TEXT
);
此表表示员工层级结构，其中 manager_id 引用员工经理的 id。在 sqlite> 提示符下执行上述命令。
接下来，将一些示例数据插入到 employees_hierarchy 表中：
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');
在这里，Alice 是 CEO（没有经理），Bob 和 Charlie 向 Alice 汇报，David 向 Bob 汇报，Eve 向 Charlie 汇报，Frank 向 David 汇报，Grace 向 Eve 汇报。在 sqlite> 提示符下执行上述命令。
现在，让我们构建一个递归 CTE，以检索 Alice（CEO）下的整个层级结构。执行以下 SQL 语句：
WITH RECURSIVE
EmployeeHierarchy(id, name, manager_id, title, level) AS (
-- Anchor member: Select the CEO
SELECT id, name, manager_id, title, 0 AS level
FROM employees_hierarchy
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: Select employees reporting to the current level
SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
FROM employees_hierarchy e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, title, level
FROM EmployeeHierarchy;
此查询定义了一个名为 EmployeeHierarchy 的递归 CTE。锚成员选择 CEO（其中 manager_id 为 NULL）。递归成员将 employees_hierarchy 表与 EmployeeHierarchy CTE 连接，以查找向上一个级别中选择的员工汇报的员工。level 列跟踪层级结构中的深度。
预期输出：
1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3
这显示了整个员工层级结构，其中 level 指示汇报级别。
在复杂查询中集成 CTE
在这一步中，你将学习如何在 SQLite 中将 CTE 集成到更复杂的查询中。你将了解如何在单个查询中使用多个 CTE。
你可以在单个查询中定义多个 CTE，方法是用逗号分隔它们。这允许你将复杂的查询分解为几个逻辑步骤，每个步骤由一个 CTE 表示。
让我们创建一个名为 department_salaries 的新表，其结构如下：
CREATE TABLE department_salaries (
department TEXT,
total_salary INTEGER
);
此表将存储每个部门的总薪资。在 sqlite> 提示符下执行上述命令。
现在，让我们使用 CTE 来计算每个部门的总薪资，并将结果插入到 department_salaries 表中。执行以下 SQL 语句：
WITH
DepartmentTotalSalaries AS (
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
)
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;
SELECT * FROM department_salaries;
这个查询首先定义一个名为 DepartmentTotalSalaries 的 CTE，它使用 employees 表计算每个部门的总薪资。然后，它将 DepartmentTotalSalaries CTE 中的结果插入到 department_salaries 表中。最后，它从 department_salaries 表中选择所有数据以显示结果。
预期输出：
Sales|105000
Marketing|122000
Engineering|70000
这显示了每个部门的总薪资。
总结
在这个实验中，你已经学习了如何在 SQLite 中定义和使用公共表表达式（Common Table Expressions，CTE）。你从简单的 CTE 开始，从表中选择数据，然后转向递归 CTE 来查询分层数据。最后，你学习了如何将 CTE 集成到更复杂的查询中。对于编写更清晰、更高效和更易于理解的 SQL 代码，CTE 是一种强大的工具。
