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

问题描述

我今天下午刚刚发现 CTE，在享受了 2 个小时之后，我意识到它们并没有像我们在任何其他语言中学到的那样执行常见的递归.

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)

为了清楚起见，我不想:

To make things clear, I don't want :

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

但是

1. 团队 1
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屋！