c# - FOREACH 递归 SQL 语句

Question

我遇到了一个看起来很简单的问题，但迄今为止我尝试过的解决方案让我在执行方面有所欠缺。在小型 (<10000) 数据集上速度似乎不错，但随着计数的增加，速度很快就会越来越长。

在 SQL Server 2008 R2 中，我有一个包含四列的表:Id、ParentId、ControlNum、ParentControlNum。

填写Id和ParentId信息。Id总是有值的，如果该行没有父行，ParentId为null，否则为表示父行的表中的一个Id的值。

问题是 Id 和 ParentId 到处都是。所有的 ID 都被添加到表中，然后处理它们以添加子项。这是问题的一部分，不是可以改变的。

我需要做的是生成 ControlNum 值以遵守父子关系。我当前的逻辑使用一些 C# 和 SQL SELECT/UPDATE 命令来完成此操作，但如前所述，性能是一个很大的问题。

伪代码

Select all Id's where the parent Id is null (All root entries)
Foreach (Id)
   GenerateControlNum(Id, CurrentCounterValue, CurrentCounterValue)

GenerateControlNum(Id, CurrentCounterValue, ParentCounterValue)
    Set Id's ControlNum to CurrentCounterValue
    Set Id's ParentControlNum to CurrentCounterValue

    Increment CurrentCounterValue

    Select All Id's where ParentId == Id (All my direct children)
    Foreach (ChildId)
        GenerateControlNum(ChildId, CurrentCounterValue, Id's ControlNum);

Baseline 正在尝试使此执行速度更快，理想情况下最好完全使用 SQL。我正在尝试按照填充有 RootId 的 CTE 的思路，然后使用 MERGE 语句遍历它们，但我似乎无法让计数器值正常工作以设置 ControlNum 值。

这在 SQL 中是否可行，或者这是否过于程序化？

当前运行方式的示例表数据:之前

ID                                      ParentId                                ControlNum  ParentControlNum
8C821027-A6F9-E011-AB48-B499BAE13A62    756F981E-A6F9-E011-AB48-B499BAE13A62    0           NULL
D7DB6033-A6F9-E011-AB48-B499BAE13A62    756F981E-A6F9-E011-AB48-B499BAE13A62    0           NULL
D2E36033-A6F9-E011-AB48-B499BAE13A62    C9E36033-A6F9-E011-AB48-B499BAE13A62    0           NULL
8FE66033-A6F9-E011-AB48-B499BAE13A62    58E66033-A6F9-E011-AB48-B499BAE13A62    0           NULL
37EC6033-A6F9-E011-AB48-B499BAE13A62    2FEC6033-A6F9-E011-AB48-B499BAE13A62    0           NULL
41EC6033-A6F9-E011-AB48-B499BAE13A62    2FEC6033-A6F9-E011-AB48-B499BAE13A62    0           NULL 
DDED6033-A6F9-E011-AB48-B499BAE13A62    BCED6033-A6F9-E011-AB48-B499BAE13A62    0           NULL
DC69981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
166A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
4D6A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
856A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
F56A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
2E6B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
666B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL
9D6B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           NULL

之后

ID                                      ParentId                                ControlNum  ParentControlNum
8C821027-A6F9-E011-AB48-B499BAE13A62    756F981E-A6F9-E011-AB48-B499BAE13A62    22          21
D7DB6033-A6F9-E011-AB48-B499BAE13A62    756F981E-A6F9-E011-AB48-B499BAE13A62    24          21
D2E36033-A6F9-E011-AB48-B499BAE13A62    C9E36033-A6F9-E011-AB48-B499BAE13A62    58          57
8FE66033-A6F9-E011-AB48-B499BAE13A62    58E66033-A6F9-E011-AB48-B499BAE13A62    69          68
37EC6033-A6F9-E011-AB48-B499BAE13A62    2FEC6033-A6F9-E011-AB48-B499BAE13A62    86          85
41EC6033-A6F9-E011-AB48-B499BAE13A62    2FEC6033-A6F9-E011-AB48-B499BAE13A62    88          85
DDED6033-A6F9-E011-AB48-B499BAE13A62    BCED6033-A6F9-E011-AB48-B499BAE13A62    95          94
DC69981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    0           0
166A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    1           1
4D6A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    2           2
856A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    3           3
F56A981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    4           4
2E6B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    5           5
666B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    6           6
9D6B981E-A6F9-E011-AB48-B499BAE13A62    NULL                                    7           7

我现在拥有的数据集是 104 个条目，所以这只是前 15 个。没有父对象的对象在底部，那些是根条目的示例，它们的控制编号和父控制编号设置为相同值(value)。在表的顶部，我们看到一些对象具有相同的父对象，因此具有匹配的父控制编号和相当接近的控制编号本身(例如，ControlNum 22 和 24 之间必须有一行也来自父 21。86 相同到 88 跳，他们只是不在彼此相邻的表格中)。

希望这能让它更清楚。

编辑:根据 Mikael 给出的答案更加清晰

下面是根据 Id 和 ParentId 信息在层次结构中显示的 ControlNum 值。通常这些会被列为 1、2、3 ... 8，但更容易避免到处都是(子)消息的显示。

1
  4
    7
    8
  5
2
  6
3

我需要的是

1
  2
    3
    4
  5
6
  7
8

这就是为什么递归一直是我一直采用的方式，我需要将 ControlNum 分配给根对象，然后下一个对象需要是它的第一个子对象，然后是该对象的子对象，依此类推，然后再继续到下一个根对象。

我想我想说的是广度优先，我需要的是深度优先。

Answer 1

不确定我是否满足您的所有要求，但这是一个开始。告诉我它是否按照您的要求运行，或者数字是否未正确生成。

;with C as
(
  select ID,
         ParentID,
         ControlNum,
         ParentControlNum,
         row_number() over(order by ParentID, ID) - 1 as rn
  from YourTable
)  
update C1
set ControlNum = C1.rn,
    ParentControlNum = case when C1.ParentID is null 
                         then C1.rn
                         else C2.rn 
                       end
from C as C1
  left outer join C as C2
    on C1.ParentID = C2.ID

在 SE-Data 上运行它，稍微修改一下输入:https://data.stackexchange.com/stackoverflow/q/115625/

版本 2

首先是递归 CTE R ，它构建了一个字符串，在为 ControlNum 生成值时用作排序依据.之后就和上面差不多了。

;with R as
(
  select ID,
         ParentID,
         cast(ID as varchar(max)) as Sort
  from YourTable
  where ParentID is null
  union all
  select T.ID,
         T.ParentID,
         R.Sort+cast(T.ID as varchar(max))
  from YourTable as T
    inner join R
      on R.ID = T.ParentID
),
C as
(
  select ID,
         ParentID,
         row_number() over(order by Sort) - 1 as rn
  from R
)  
update T
set ControlNum = C1.rn,
    ParentControlNum = case when C1.ParentID is null 
                         then C1.rn
                         else C2.rn 
                       end
from YourTable as T
  inner join C as C1
    on T.ID = C1.ID
  left outer join C as C2
    on T.ParentID = C2.ID

在这里测试:https://data.stackexchange.com/stackoverflow/q/115626/

注意:我想这是您对某些数据所做的一次性事情，因为您将很难添加新节点并同时保持这样的编号。例如，如果您将一个新的子节点添加到第一个节点，您将必须分配所有 ControlNum += 1对于“下方”的所有节点并重新分配所有 ParentControlNum .