SQL 层级关系的 SQL

在本文中，我们将介绍如何使用 SQL 查询和处理层级关系的数据。层级关系是指数据中存在父子关系的结构，例如组织架构、产品分类等。我们将从基本的层级查询开始，然后介绍如何处理层级关系的增删改操作，并探讨一些查询层级关系数据的高级技巧。

阅读更多：SQL 教程

基本层级查询

对于具有层级关系的数据，我们经常需要查询某个节点的所有子节点，或者查询一个节点的直接父节点。SQL 提供了一些关键字和函数来实现这些查询。

首先，我们介绍 SELECT 语句中的 CONNECT BY 子句。该子句用于指定层级关系的连接条件。例如，我们有一个 departments 表，其中包含部门的 ID、名称和父部门的 ID：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    parent_department_id INT
);

INSERT INTO departments (department_id, department_name, parent_department_id)
VALUES (1, '总部', NULL),
       (2, '财务部', 1),
       (3, '人力资源部', 1),
       (4, '市场部', 1),
       (5, '财务一部', 2),
       (6, '财务二部', 2),
       (7, '市场一部', 4),
       (8, '市场二部', 4),
       (9, '市场二部子部门', 8);

要查询某个节点的所有子节点，我们可以使用以下 SQL 语句：

SELECT department_id, department_name
FROM departments
START WITH department_id = 4
CONNECT BY PRIOR department_id = parent_department_id;

在以上示例中，我们指定了起始节点为 department_id = 4 的行，使用 PRIOR 关键字来连接父子节点，查询结果如下：

DEPARTMENT_ID  DEPARTMENT_NAME
4              市场部
7              市场一部
8              市场二部
9              市场二部子部门

要查询一个节点的直接父节点，我们可以使用 CONNECT_BY_ISLEAF 伪列。该伪列的值为 0 表示当前节点有子节点，为 1 表示当前节点为叶子节点。例如，以下 SQL 语句用于查询 department_id = 7 的节点的直接父节点：

SELECT department_id, department_name
FROM departments
START WITH department_id = 7
CONNECT BY PRIOR parent_department_id = department_id;

查询结果如下：

DEPARTMENT_ID  DEPARTMENT_NAME
7              市场一部
4              市场部
1              总部

层级关系的增删改操作

除了查询，我们还需要了解如何进行层级关系的增删改操作。

对于插入一条新的层级关系记录，我们需要指定父节点的 ID，然后为新记录分配一个唯一的 ID。例如，以下 SQL 语句用于在 departments 表中插入一个名为 “技术部” 的子部门，其父部门为 “总部”（department_id = 1）：

INSERT INTO departments (department_id, department_name, parent_department_id)
SELECT MAX(department_id) + 1, '技术部', department_id
FROM departments
WHERE department_name = '总部';

要删除层级关系的记录，我们可以使用 DELETE 语句。由于涉及到子节点的情况，我们需要使用递归删除的方式。以下 SQL 语句用于删除 department_id = 4（市场部）的节点及其所有子节点：

DELETE FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM departments
    START WITH department_id = 4
    CONNECT BY PRIOR department_id = parent_department_id
);

对于修改层级关系的记录，我们需要更新该记录的 parent_department_id 字段。例如，以下 SQL 语句用于将 department_id = 8（市场二部）的节点移动到 department_id = 5（财务一部）下面：

UPDATE departments
SET parent_department_id = 5
WHERE department_id = 8;

高级层级查询技巧

除了基本的层级查询操作，还有一些高级技巧可以帮助我们更灵活地查询和处理层级关系的数据。

首先，我们介绍 LEVEL 伪列。该伪列表示当前行在层级关系中的级别，根节点为 1，依次递增。例如，以下 SQL 语句用于查询每个节点所在的层级：

SELECT department_id, department_name, LEVEL
FROM departments
START WITH department_id = 1
CONNECT BY PRIOR department_id = parent_department_id;

查询结果如下：

DEPARTMENT_ID  DEPARTMENT_NAME  LEVEL
1              总部              1
2              财务部            2
5              财务一部          3
6              财务二部          3
3              人力资源部        2
4              市场部            2
7              市场一部          3
8              市场二部          3
9              市场二部子部门    4

其次，我们介绍 CONNECT_BY_ROOT 函数。该函数返回从当前行到根节点之间的路径，可以用于展示层级结构和生成层级关系的路径。例如，以下 SQL 语句用于查询每个节点及其从根节点到该节点的路径：

SELECT department_id, department_name, CONNECT_BY_ROOT department_name AS full_path
FROM departments
START WITH department_id = 7
CONNECT BY PRIOR parent_department_id = department_id;

查询结果如下：

DEPARTMENT_ID  DEPARTMENT_NAME  FULL_PATH
7              市场一部          总部/市场部/市场一部
4              市场部            总部/市场部
1              总部              总部

总结

本文介绍了如何使用 SQL 查询和处理层级关系的数据。我们学习了基本的层级查询语句，如 CONNECT BY 子句和 CONNECT_BY_ISLEAF 伪列，并实践了层级关系的增删改操作。此外，我们还了解了一些高级层级查询技巧，如 LEVEL 伪列和 CONNECT_BY_ROOT 函数。通过掌握这些知识，我们能够更好地处理和分析具有层级关系的数据。

希望本文对您理解和应用 SQL 处理层级关系的操作有所帮助！