SQL Server中递归自连接如何高效检索分层数据？

在 SQL Server 中使用递归自连接高效检索分层数据

SQL Server 的递归自连接提供了一种在单个表中导航分层数据结构的有效方法。 该技术涉及遍历表的行以根据层次关系收集相关数据。 以下查询演示了一种简化的方法：

<code class="language-sql">WITH q AS (
    SELECT *
    FROM mytable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q;</code>

此查询使用名为“q”的递归公用表表达式 (CTE)。 CTE 首先选择 ParentID 为 NULL 的所有行，代表层次结构中的顶级节点。 随后，它递归地将 mytable 与 CTE 本身连接起来，根据 ParentID 和 PersonID 列添加子行。这个迭代过程一直持续到所有后代都被包括在内。

考虑这个示例mytable：

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2
5	YT	NULL
6	IS	5

检索 CJ 的层次结构（其中 PersonID = 1）产生：

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2

类似地，EB 的层次结构 (PersonID = 2) 将返回：

PersonID	Initials	ParentID
2	EB	1
4	SW	2

为了维持层次结构顺序，包含排序条件的修改查询是有益的：

<code class="language-sql">WITH q AS (
    SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
    FROM mytable m
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY bc;</code>

此增强型查询向 CTE 引入了 bc 列，表示从每个节点到根的路径。 按 bc 对结果进行排序可确保保留分层结构（包括同级顺序）。 可以自定义 ORDER BY 子句以根据需要调整兄弟排序。

以上是SQL Server中递归自连接如何高效检索分层数据？的详细内容。更多信息请关注PHP中文网其他相关文章！