如何在 SQL Server 中使用递归自连接检索类别及其祖先？

在 SQL Server 中使用递归自连接来检索类别及其祖先

本文演示了如何使用递归自连接从 SQL Server 中的分层表中检索类别及其祖先类别。 我们将使用通用表表达式 (CTE) 来完成此任务。

场景：

考虑一个 Categories 表，其中 Id、Name 和 ParentId 列表示分层类别结构。 目标是编写一个查询，以单个逗号分隔的字符串返回特定类别（例如“商务笔记本电脑”）及其所有父类别。

递归 CTE 解决方案：

该解决方案采用递归 CTE 来遍历层次结构。 这是查询：

WITH CategoryHierarchy AS (
    SELECT id, name, CAST(name AS VARCHAR(MAX)) AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL  -- Start with root categories
    UNION ALL
    SELECT c.id, c.name, CAST(ch.path + ',' + c.name AS VARCHAR(MAX)), c.parent_id
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)

此 CTE CategoryHierarchy 递归地将 Categories 表与其自身连接起来。初始的 SELECT 语句选择根类别（parent_id 为 NULL 的类别）。 UNION ALL 将其与后续的递归选择相结合，通过连接父类别名称和子类别名称来构建 path 字符串。

检索特定类别和祖先的查询：

要检索“商务笔记本电脑”类别及其祖先：

SELECT id, name, path
FROM CategoryHierarchy
WHERE name = 'Business Laptops';

示例表和数据：

让我们创建一个示例 Categories 表并插入一些数据：

CREATE TABLE Categories (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentId INT REFERENCES Categories(Id)
);

INSERT INTO Categories (Id, Name, ParentId)
VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Business Laptops', 2),
(5, 'Gaming Laptops', 2);

预期结果：

对于针对“商务笔记本电脑”的查询，预期输出为：

<code>id      name                path
4       Business Laptops     Electronics,Laptops,Business Laptops</code>

这种方法使用递归 CTE 有效地检索类别及其完整谱系，为在 SQL Server 中导航分层数据提供了清晰且高效的解决方案。 如果您的类别名称可能超出此限制，请记住调整 VARCHAR(MAX) 长度。

以上是如何在 SQL Server 中使用递归自连接检索类别及其祖先？的详细内容。更多信息请关注PHP中文网其他相关文章！