SQL中的递归选择 [英] Recursive select in SQL

问题描述

我有一个问题，就是想不通.我知道我想要什么，只是无法在屏幕上显示出来.我有一张看起来像这样的桌子:

I have an issue I just can't get my head around. I know what I want, just simply can't get it out on the screen. What I have is a table looking like this:

Id, PK UniqueIdentifier, NotNull
Name, nvarchar(255), NotNull
ParentId, UniqueIdentifier, Null

ParentId 有一个 FK 到 Id.

ParentId have a FK to Id.

我想要完成的是获取我传入的 ID 下方所有 ID 的平面列表.

What I want to accomplish is to get a flat list of all the id's below the Id I pass in.

示例:

1   TestName1    NULL
2   TestName2    1
3   TestName3    2
4   TestName4    NULL
5   TestName5    1

树看起来像这样:

-1
  -> -2
       -> -3
  -> -5
-4

如果我现在要求 4，我只会得到 4，但如果我要求 1，我会得到 1、2、3 和 5.如果我要求 2，我会得到 2 和 3，依此类推.

If I now ask for 4, I would only get 4 back, but if I ask for 1 I would get 1, 2, 3 and 5. If I ask for 2, I would get 2 and 3 and so on.

有没有人可以指出我正确的方向.我的大脑被炸了，所以我很感激我能得到的所有帮助.

Is there anyone who can point me in the right direction. My brain is fried so I appreciate all help I can get.

推荐答案

declare @T table(
  Id int primary key,
  Name nvarchar(255) not null,
  ParentId int)

insert into @T values
(1,   'TestName1',    NULL),
(2,   'TestName2',    1),
(3,   'TestName3',    2),
(4,   'TestName4',    NULL),
(5,   'TestName5',    1)

declare @Id int = 1

;with cte as
(  
  select T.*
  from @T as T
  where T.Id = @Id
  union all
  select T.*
  from @T as T
    inner join cte as C
      on T.ParentId = C.Id
)
select *
from cte      

结果

Id          Name                 ParentId
----------- -------------------- -----------
1           TestName1            NULL
2           TestName2            1
5           TestName5            1
3           TestName3            2

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