CTE真正的递归？ [英] A real recursion with CTE?

问题描述

我今天下午才发现CTE，在享受了两个小时的学习后，我意识到他们并没有像我们所有人从其他任何语言中学到的那样进行普通的递归。

I just discovered CTE this afternoon and after enjoying them during 2 hours, I realized that they did not perform a common recursion like we all learned in any other language.

我的意思是，我总是看到像树搜索一样的递归。因此，我期望CTE一直走到它发现的第一片叶子，但没有。他按层次工作。它从头开始，然后是所有分支，然后是所有子分支，等等...然后是叶子。

What I mean is, I always see recursion like a tree search. So i was expecting CTE to go all the way down to the first leaf it founds, but no. He works by layers. It begins by the head, then all the branches, then all the sub-branches, etc... and THEN the leaves.

有没有办法让它进行搜索不同吗？也许我错过了一些事情...
我在SQL Server 2005上工作（非，我不能在2008年更改）

Is there a way to make it search differently ? Perhaps did I miss something... I work on SQL Server 2005 (non, I can't change for the 2008)

我不要：

1. team1


2. team2


3. team3


4. team1-1


5. team3-1


6. team1-2

1. team1
2. team2
3. team3
4. team1-1
5. team3-1
6. team1-2

但是

1. team1


2. team1-1


3. team1-2


4. team2


5. team3


6. team3-1

1. team1
2. team1-1
3. team1-2
4. team2
5. team3
6. team3-1

谢谢

推荐答案

您可以构建列以进行递归时进行排序。

You can build a column to sort by when you do the recursion.

类似这样的东西：

declare @t table
(
  ID int,
  ParentID int,
  Name varchar(10)
);

insert into @T values
(1, null, 'team1'),
(2, null, 'team2'),
(3, null, 'team3'),
(4, 1, 'team1-1'),
(5, 1, 'team1-2'),
(6, 3, 'team3-1');

with C as
(
  select T.ID,
         T.ParentID,
         T.Name,
         cast(right(100000 + row_number() over(order by T.ID), 5) as varchar(max)) as Sort
  from @T as T
  where T.ParentID is null
  union all
  select T.ID,
         T.ParentID,
         T.Name,
         C.Sort+right(100000 + row_number() over(order by T.ID), 5)
  from @T as T
    inner join C
      on T.ParentID = C.ID
)
select *
from C
order by Sort

结果：

ID          ParentID    Name       Sort
----------- ----------- ---------- ------------
1           NULL        team1      00001
4           1           team1-1    0000100001
5           1           team1-2    0000100002
2           NULL        team2      00002
3           NULL        team3      00003
6           3           team3-1    0000300001

这篇关于CTE真正的递归？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！