FOREACH递归SQL语句

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

在SQLServer2008R2中，我有一个包含四列的表：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个。没有父对象的对象位于底部，这些对象是根条目的示例，它们的控件号和父控件号设置为相同的值。在表的顶部，我们看到一些对象具有相同的父对象，因此具有匹配的父控制号和相当接近的控制号(例如，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，然后下一个对象需要是它的第一个子对象，然后是这些对象的子对象，以此类推，然后再转到下一个根对象。

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

;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

;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