c# - FOREACH递归SQL语句

我有一个看似简单的问题，但迄今为止我尝试过的解决方案使我在执行领域缺乏需求。在小型（
在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。我正在尝试使用RootIds填充CTE，然后使用MERGE语句遍历它们，但似乎无法使计数器值正确设置ControlNum值。

在SQL中这是否有可能，或者这太多是过程类型的处理？

当前运行方式的示例表数据：BEFORE

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

在输入略微修改的SE-Data上运行它：http://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

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

注意：我想这是一次性处理某些数据的事情，因为添加新节点会很困难，同时又要维护这样的编号。例如，如果将新的子节点添加到第一个节点，则必须为“以下”的所有节点分配所有ControlNum += 1，然后重新分配所有ParentControlNum。

关于c# - FOREACH递归SQL语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7862648/