如何递归读取所有记录并按级别深度显示TSQL [英] How to read all records recursively and show by level depth TSQL

问题描述

是否有一种方法可以按深度级别递归读取相似的表和顺序中的记录?

Is there a way to read records recursively in similar table and order by depth level?

#table:

id int    |   parent int    |   value string
--------------------------------------------
1             -1                some
2             1                 some2
3             2                 some3
4             2                 some4
5             3                 some5
6             4                 some6
7             3                 some5
8             3                 some5
9             8                 some5
10            8                 some5

因此，有一种方法可以递归选择结果表的外观.

So is there a way to recursively select where result table would look like this.

select * from #table where id=3 

id int      | parent int      | value string   |  depth  
--------------------------------------------------------
3             2                 some3             0
5             3                 some5             1
7             3                 some5             1 
8             3                 some5             1
9             8                 some5             2
10            8                 some5             2

因此，如果我选择id = 3，我将看到id = 3和孩子的递归

So if I choose id=3 I would see recursion for id=3 and children

谢谢

推荐答案

;with C as
(
  select id,
         parent,
         value,
         0 as depth
  from YourTable
  where id = 3
  union all
  select T.id,
         T.parent,
         T.value,
         C.depth + 1
  from YourTable as T
    inner join C  
      on T.parent = C.id
)
select *
from C

SE数据

这篇关于如何递归读取所有记录并按级别深度显示TSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！