递归SQL表中新增Level列的最优实现方案咨询

问题背景 #

我有一张树形结构的数据表，初始结构如下：

现在希望新增Level列，用数字表示每个节点的层级（根节点为0，子节点层级=父节点层级+1），最终期望得到的结果如下：

想请教一下，新增Level列的最佳方案是什么？是新增列并添加公式、使用计算列还是函数？

方案分析与最佳选择 #

1. 新增列+手动/批量公式

这种方式适合一次性计算层级、后续数据几乎不变的场景，比如Excel里可以用递归查找公式：

=IF(B2="root",0,XLOOKUP(B2,$A$2:$A$100,$D$2:$D$100,0)+1)

但缺点很突出：

• 数据更新（新增节点、修改父节点）后，公式需要手动刷新，容易遗漏出错
• 递归公式在数据量较大时会明显卡顿，甚至出现循环引用问题

2. 使用计算列（适配SQL数据库/Excel表格）

这是更灵活的方案，能自动维护层级值：

• SQL数据库（以SQL Server为例）：
  如果是SQL Server 2016+，可以利用hierarchyid类型直接推导层级；也可以先用递归CTE初始化层级，再设置持久化计算列：

  -- 第一步：用递归CTE计算所有节点的层级
  WITH RecursiveCTE AS (
      SELECT ID, ParentID, Item, 0 AS Level
      FROM YourTable
      WHERE ParentID = 'root'
      UNION ALL
      SELECT t.ID, t.ParentID, t.Item, r.Level + 1
      FROM YourTable t
      JOIN RecursiveCTE r ON t.ParentID = CAST(r.ID AS VARCHAR(10))
  )
  -- 第二步：将层级值写入新增的Level列
  UPDATE YourTable
  SET Level = r.Level
  FROM YourTable t
  JOIN RecursiveCTE r ON t.ID = r.ID;
  
  -- 可选：设置为持久化计算列，提升后续查询性能
  ALTER TABLE YourTable ALTER COLUMN Level INT PERSISTED;
  

  要是数据库支持hierarchyid，直接用内置的GetLevel()方法更省心，数据变更时层级会自动更新。
• Excel环境：
  开启表格功能后添加计算列，公式会自动应用到整列，新增行时自动计算。建议用XLOOKUP替代旧版的VLOOKUP，性能会更稳定。

这种方案的核心优势是自动维护，数据变更时无需手动重新计算，适配大多数日常场景。

3. 使用自定义函数（适配SQL/Excel）

• SQL中：可以写标量值函数递归查询父节点计算层级，但标量函数性能较差，大数据量下不推荐：

  CREATE FUNCTION dbo.GetNodeLevel(@ID INT)
  RETURNS INT
  AS
  BEGIN
      DECLARE @Level INT = 0;
      DECLARE @ParentID VARCHAR(10);
      SELECT @ParentID = ParentID FROM YourTable WHERE ID = @ID;
      WHILE @ParentID <> 'root'
      BEGIN
          SET @Level = @Level + 1;
          SELECT @ParentID = ParentID FROM YourTable WHERE ID = CAST(@ParentID AS INT);
      END
      RETURN @Level;
  END;
  
  -- 查询时调用：SELECT ID, ParentID, Item, dbo.GetNodeLevel(ID) AS Level FROM YourTable;
  

• Excel中：可以写VBA自定义函数，但需要启用宏，对普通用户不够友好。

最终最佳方案总结 #

• SQL数据库环境：
  优先选择递归CTE初始化层级+持久化计算列，如果数据库支持hierarchyid类型，直接用它的内置层级计算会更高效，能自动处理节点的新增、移动等变更操作。
• Excel环境：
  数据量小且不频繁更新：用新增列+递归公式即可；
  数据需要频繁变更：开启表格的计算列功能，配合XLOOKUP提升性能。

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