sql server 递归查询所有子节点

SQL Server递归查询所有子节点

在关系型数据库中，经常需要查询树形结构数据的所有子节点。SQL Server提供了递归查询的功能，可以方便地查询出树形结构中的所有子节点。本文将介绍如何使用SQL Server进行递归查询，并提供相关的代码示例。

什么是递归查询

递归查询是一种通过递归算法从一个给定的起始节点开始，不断向下查询其子节点的过程。在树形结构中，递归查询可以用来查找指定节点的所有子节点。

使用递归查询实现树形结构的遍历

假设我们有一个名为departments的表，它包含了公司的组织结构信息。表的结构如下所示：

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT NULL
);

表中的id字段表示部门的唯一标识符，name字段表示部门的名称，parent_id字段表示部门的父节点。如果parent_id字段为NULL，则表示该部门是根节点。

下面是departments表的示例数据：

id	name	parent_id
1	公司	NULL
2	人事部	1
3	财务部	1
4	开发部	1
5	前端开发组	4
6	后端开发组	4
7	测试部	1
8	测试组	7

我们希望查询出公司部门的所有子节点。可以使用递归查询来实现这个需求。

使用递归查询查询所有子节点

在SQL Server中，可以使用CTE（Common Table Expression）来实现递归查询。CTE是一个临时的命名结果集，可以在SELECT、INSERT、UPDATE和DELETE语句中引用。

要实现递归查询，需要使用CTE的递归部分来定义查询的递归关系。下面是一个示例代码：

WITH recursive_departments AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 1 -- 指定起始节点为公司部门
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM departments AS d
    INNER JOIN recursive_departments AS rd ON d.parent_id = rd.id
)
SELECT id, name, parent_id
FROM recursive_departments;

上述代码中，recursive_departments是一个CTE的名称。在CTE的初始部分，我们选择了起始节点为id = 1的部门。在递归部分，我们通过INNER JOIN将departments表与recursive_departments进行连接，连接条件为d.parent_id = rd.id，这样就可以找到所有的子节点。

执行以上查询语句，将会返回如下结果：

id	name	parent_id
1	公司	NULL
2	人事部	1
3	财务部	1
4	开发部	1
5	前端开发组	4
6	后端开发组	4
7	测试部	1
8	测试组	7

可以看到，通过递归查询，我们成功地找到了公司部门的所有子节点。

将递归查询结果转化为树形结构

为了更好地可视化树形结构，我们可以将递归查询的结果转化为树形结构。下面是一个示例代码，可以将递归查询的结果转化为带有层级关系的树形结构：

WITH recursive_departments AS (
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE id = 1 -- 指定起始节点为公司部门
    UNION ALL
    SELECT