如何在SQL Server中查询多层级大数据量树形数据并实现Angular搜索展示

一、SQL端：高效获取匹配节点的完整路径 #

针对大数据量深层级的场景，优化递归CTE写法并添加索引，解决之前的失效问题：

1. 添加必要索引 #

-- 给父/子节点ID建复合索引，加速递归遍历
CREATE NONCLUSTERED INDEX IX_tbl1_ParentChild ON tbl1(col_b, col_a) INCLUDE(col_c, col_d);
-- 给搜索字段建索引，加速关键词匹配
CREATE NONCLUSTERED INDEX IX_tbl1_Search ON tbl1(col_c) INCLUDE(col_a, col_b, col_d);

2. 编写搜索存储过程 #

该存储过程返回所有匹配关键词的节点，以及每个节点的完整父路径（包含路径上所有节点信息）：

CREATE PROCEDURE usp_SearchTreeNodes
    @SearchKeyword NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    WITH RecursivePath AS (
        -- 锚点：匹配搜索关键词的节点
        SELECT 
            col_a AS NodeId,
            col_b AS ParentId,
            col_c AS NodeText,
            col_d AS Level,
            CAST(col_a AS NVARCHAR(MAX)) AS PathIds,
            CAST(col_c AS NVARCHAR(MAX)) AS PathText
        FROM tbl1
        WHERE col_c LIKE '%' + @SearchKeyword + '%'
        
        UNION ALL
        
        -- 递归：向上遍历父节点
        SELECT 
            t.col_a AS NodeId,
            t.col_b AS ParentId,
            t.col_c AS NodeText,
            t.col_d AS Level,
            CAST(t.col_a + '→' + rp.PathIds AS NVARCHAR(MAX)) AS PathIds,
            CAST(t.col_c + '→' + rp.PathText AS NVARCHAR(MAX)) AS PathText
        FROM tbl1 t
        INNER JOIN RecursivePath rp ON t.col_a = rp.ParentId
        WHERE t.col_a IS NOT NULL -- 排除根节点（根节点ParentId请根据实际数据调整）
    )
    SELECT 
        NodeId,
        ParentId,
        NodeText,
        Level,
        PathIds,
        PathText
    FROM RecursivePath
    ORDER BY PathIds
    OPTION (MAXRECURSION 0); -- 允许无限递归，适配15层以上结构
END

注：若根节点ParentId为固定值（如示例中的17对应ParentId为NULL/0），可调整递归部分的WHERE条件，减少无效遍历。

二、前端Angular适配：搜索结果的树形展示 #

基于现有懒加载组件，做以下修改支持搜索功能：

1. 搜索逻辑流程 #

• 用户输入关键词时，调用上述存储过程的API，获取匹配节点的完整路径数据。
• 将返回数据转换为树形结构：遍历每个路径的节点信息，依次构建父→子节点关系，同时标记匹配节点用于高亮。
• 自动展开所有匹配节点的完整路径，方便用户查看。

2. 组件适配代码片段 #

// 将搜索结果转换为树形结构
transformToTree(searchResults: any[], keyword: string): TreeNode[] {
    const nodeMap = new Map<number, TreeNode>();
    const rootNodes: TreeNode[] = [];

    searchResults.forEach(item => {
        const node: TreeNode = {
            id: item.NodeId,
            parentId: item.ParentId,
            label: item.NodeText,
            level: item.Level,
            isMatch: item.NodeText.includes(keyword),
            children: [],
            expanded: false
        };

        nodeMap.set(item.NodeId, node);

        if (item.ParentId) {
            const parentNode = nodeMap.get(item.ParentId);
            parentNode?.children.push(node);
        } else {
            rootNodes.push(node);
        }
    });

    // 递归展开所有路径节点
    this.expandAllPaths(rootNodes);
    return rootNodes;
}

expandAllPaths(nodes: TreeNode[]): void {
    nodes.forEach(node => {
        node.expanded = true;
        if (node.children?.length) {
            this.expandAllPaths(node.children);
        }
    });
}

3. 功能兼容 #

• 保留原有懒加载逻辑：正常浏览时仍按需加载子节点。
• 搜索结果展示时，仅渲染匹配节点的路径树，兼顾性能与需求；匹配节点支持点击加载未加载的子节点（复用原有API）。

三、关键优化点 #

• SQL端：通过索引大幅提升递归CTE性能，避免大数据量下超时；OPTION (MAXRECURSION 0)适配深层级结构。
• 前端：仅生成匹配节点的路径树而非全量数据，复用原有组件逻辑，无需重构。

内容的提问来源于stack exchange，提问作者bucket