SQL 层次查询

在SQL中，层次查询（Hierarchical Query）是一种用于处理树形结构数据的强大工具。树形结构数据通常表现为父子关系，例如组织结构、文件系统或分类目录。层次查询允许我们以递归的方式遍历这些结构，从而提取出所需的信息。

什么是层次查询？

层次查询是一种递归查询，用于处理具有层次结构的数据。它通过递归地遍历父子关系，生成一个层次化的结果集。常见的应用场景包括：

组织结构：查询某个员工的所有下属。
文件系统：查询某个目录下的所有子目录和文件。
分类目录：查询某个分类的所有子分类。

基本语法

在SQL中，层次查询通常使用 WITH RECURSIVE 或 CONNECT BY 语法来实现。不同的数据库管理系统（DBMS）可能支持不同的语法。下面我们将分别介绍这两种语法。

使用 `WITH RECURSIVE`

WITH RECURSIVE 是SQL标准中用于递归查询的语法，适用于大多数现代数据库系统，如PostgreSQL、MySQL和SQLite。

sql
WITH RECURSIVE hierarchy AS (
    -- 基础查询：选择根节点
    SELECT id, name, parent_id
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：选择子节点
    SELECT e.id, e.name, e.parent_id
    FROM employees e
    INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

在这个例子中，我们首先选择根节点（parent_id IS NULL），然后递归地选择每个节点的子节点，直到没有更多的子节点为止。

使用 `CONNECT BY`

CONNECT BY 是Oracle数据库中用于层次查询的语法。它使用 START WITH 和 CONNECT BY PRIOR 来定义递归关系。

sql
SELECT id, name, parent_id
FROM employees
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

在这个例子中，START WITH 定义了根节点，CONNECT BY PRIOR 定义了父子关系。

实际案例

假设我们有一个员工表 employees，其结构如下：

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT
);

表中数据如下：

sql
INSERT INTO employees (id, name, parent_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

查询所有下属

假设我们想要查询Alice的所有下属，可以使用以下查询：

sql
WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id
    FROM employees
    WHERE name = 'Alice'

    UNION ALL

    SELECT e.id, e.name, e.parent_id
    FROM employees e
    INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

输出结果如下：

id	name	parent_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	2
6	Frank	3

查询层级深度

我们还可以查询每个员工的层级深度：

sql
WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM employees
    WHERE name = 'Alice'

    UNION ALL

    SELECT e.id, e.name, e.parent_id, h.level + 1
    FROM employees e
    INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

输出结果如下：

id	name	parent_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Charlie	1	2
4	David	2	3
5	Eve	2	3
6	Frank	3	3

总结

层次查询是处理树形结构数据的强大工具，它允许我们以递归的方式遍历父子关系。通过 WITH RECURSIVE 或 CONNECT BY 语法，我们可以轻松地查询出层次结构中的所有节点及其层级深度。

附加资源

练习

修改上述查询，使其返回每个员工的完整路径（例如：Alice/Bob/David）。
尝试在MySQL或PostgreSQL中实现一个层次查询，查询某个分类的所有子分类。

提示

在实际应用中，层次查询可能会涉及大量数据，因此请确保在递归查询中设置适当的终止条件，以避免无限递归。

什么是层次查询？​

基本语法​

使用 WITH RECURSIVE​

使用 CONNECT BY​

实际案例​

查询所有下属​

查询层级深度​

总结​

附加资源​

练习​