SQL开发知识：sqlserver 树形结构查询单表实例

1. 概述

在实际的业务需求中，树形结构的存储是很常见的。在 SQL 数据库中，可以通过使用递归查询来实现树形结构的查询。本篇文章将介绍如何使用 SQL Server 数据库的单表数据来实现树形结构查询。

2. 单表存储树形结构

单表存储树形结构是指通过给每个节点增加父节点 ID 的方式将整个树形结构存储在一张表中。下面是一个示例表：

CREATE TABLE [dbo].[Organization](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ParentID] [int] NULL,
    CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

在这个表中，每个节点存储了其自身的 ID、名称和父节点的 ID。其中，根节点的父节点 ID 为 NULL。

3. 递归查询实现树形结构

3.1 查询某个节点的所有子节点

要查询某个节点的所有子节点，可以使用递归查询。先来看一个示例：

DECLARE @orgID int = 1; -- 查询 ID 为 1 的节点及其所有子节点
WITH cte AS (
    SELECT ID, Name, ParentID
    FROM Organization
    WHERE ID = @orgID
    UNION ALL
    SELECT o.ID, o.Name, o.ParentID
    FROM Organization o
    INNER JOIN cte ON cte.ID = o.ParentID
)
SELECT ID, Name, ParentID
FROM cte

在这个查询中，我们首先定义了一个公共表表达式（Common Table Expression，简称 CTE）cte，其中只包含查询指定节点的一行数据。

然后在 CTE 中，我们使用 UNION ALL 连接查询该节点的所有子节点。连接时使用了自连接，通过 cte.ID = o.ParentID 指定连接条件。通过递归地向下查询，我们最终得到了该节点及其所有子节点的数据。

3.2 查询某个节点的父节点链

类似地，我们也可以使用递归查询来查询某个节点的所有父节点。下面是一个示例：

DECLARE @orgID int = 7; -- 查询 ID 为 7 的节点的父节点链
WITH cte AS (
    SELECT ID, Name, ParentID
    FROM Organization
    WHERE ID = @orgID
    UNION ALL
    SELECT o.ID, o.Name, o.ParentID
    FROM Organization o
    INNER JOIN cte ON cte.ParentID = o.ID
)
SELECT ID, Name, ParentID
FROM cte

在这个查询中，我们同样首先定义了一个 CTE，其中只包含了查询指定节点的一行数据。在 CTE 中，我们使用 UNION ALL 连接查询该节点的所有父节点。连接时使用了自连接，通过 cte.ParentID = o.ID 指定连接条件。通过递归地向上查询，我们最终得到了该节点的父节点链。

3.3 查询整张表的树形结构

使用递归查询，我们可以很方便地实现整张表的树形结构查询。下面是一个示例：

WITH tree AS (
    SELECT ID, Name, ParentID, CAST(NULL AS NVARCHAR(50)) AS ParentName, 0 AS Depth 
    FROM Organization WHERE ParentID IS NULL
    UNION ALL
    SELECT o.ID, o.Name, o.ParentID, t.Name, Depth + 1
    FROM Organization o
    INNER JOIN tree t ON t.ID = o.ParentID
)
SELECT ID, Name, ParentID, ParentName, Depth
FROM tree
ORDER BY Depth, ID

在这个查询中，我们首先定义了一个 CTEtree，其中只包含了根节点。在 CTE 中，我们使用 UNION ALL 连接查询表中的所有节点数据。连接时使用了自连接，通过 t.ID = o.ParentID 指定连接条件。

通过递归地向下查询，我们最终得到了整张表的树形结构。在查询结果中，我们使用了CAST(NULL AS NVARCHAR(50)) AS ParentName将根节点的 ParentName 设为 NULL，并使用了一个 Depth 字段来表示节点的深度。

4. 总结

本文主要介绍了如何使用 SQL Server 数据库的单表数据来实现树形结构的查询，涵盖了递归查询某个节点的所有子节点和父节点链，以及查询整张表的树形结构三个方面。

递归查询是处理树形结构数据的常用方法，但是需要注意的是，递归查询的性能往往较低。如果对性能有较高的要求，建议考虑其他的查询方法。