SQLite CTE 查询

介绍

在这个实验中，你将探索 SQLite 中通用表表达式（Common Table Expressions，CTEs）的强大功能。你将学习如何定义和使用 CTE，以增强查询的可读性和可维护性。你将从简单的 CTE 开始，然后转向递归 CTE。完成这个实验后，你将能够使用 CTE 编写更简洁、更高效且更易于理解的 SQL 代码。

Skills Graph

创建数据库和表

在这一步中，你将创建一个 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 是一种强大的工具。