SQL Server 查找父节点所有子节点|极客教程

SQL Server 查找父节点所有子节点

1. 简介

在SQL Server数据库中，有时候我们需要查找给定父节点的所有子节点。这种场景在树状结构的数据管理中很常见，比如员工和部门之间的关系，商品分类等。本文将详细讨论如何使用SQL语句来实现这个功能。

2. 数据准备

在开始之前，我们需要有一个包含父节点和子节点关系的数据表。假设我们有一个名为Categories的表，它包含以下字段：

CREATE TABLE Categories (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    parent_id INT
);

INSERT INTO Categories (id, name, parent_id)
VALUES
    (1, '电子产品', NULL),
    (2, '手机', 1),
    (3, '电脑', 1),
    (4, '华为手机', 2),
    (5, '小米手机', 2),
    (6, '联想电脑', 3),
    (7, '戴尔电脑', 3);

以上示例数据表示了一个电子产品分类，其中包含了手机和电脑等子分类。

3. 使用递归查询

为了查找给定父节点的所有子节点，我们可以使用递归查询。SQL Server 提供了一种递归查询的方法，即使用 WITH RECURSIVE 或 COMMON TABLE EXPRESSION (CTE)。下面是一个示例：

WITH RecursiveCategories AS (
    SELECT id, name, parent_id
    FROM Categories
    WHERE parent_id = @parent_id

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM Categories c
    INNER JOIN RecursiveCategories rc ON rc.id = c.parent_id
)
SELECT *
FROM RecursiveCategories;

上述SQL语句中，我们首先选取了初始的父节点，并将其插入到临时表 RecursiveCategories 中。然后，我们使用 UNION ALL 连接 Categories 表和 RecursiveCategories 子查询，以获取父节点的所有子节点。最后，通过查询 RecursiveCategories 表，我们可以得到所有子节点的信息。

以下是一个示例查询获取 id 为 1 的父节点（电子产品）的所有子节点：

DECLARE @parent_id INT = 1;

WITH RecursiveCategories AS (
    SELECT id, name, parent_id
    FROM Categories
    WHERE parent_id = @parent_id

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM Categories c
    INNER JOIN RecursiveCategories rc ON rc.id = c.parent_id
)
SELECT *
FROM RecursiveCategories;

运行以上代码，将会返回以下结果：

 id |     name     | parent_id
----+--------------+-----------
  2 | 手机         |          1
  4 | 华为手机     |          2
  5 | 小米手机     |          2
  3 | 电脑         |          1
  6 | 联想电脑     |          3
  7 | 戴尔电脑     |          3

可以看到，我们成功地获取了父节点为 电子产品 的所有子节点。