一定范围内的 SQL 递归

Question

我有一个sql查询来形成树状视图的父/子结构，结果是这样的：

LVL1A

LVL1A/LVL2A

LVL1A/LVL2B

LVL1B/LVL2A/LVL3A

LVL1C

LVL1D/LVL2A/LVL3A/LVL4A

。

查询本身没有限制范围，例如，如果我只想获得第一级和第二级的树状视图有人可以修改SQL查询以添加此类功能吗？传统知识

;with cte as
     (
     select 
         labelID, 
         Title, 
         ParentLevel, 
         cast(Title as varchar(max)) as [treePath]
         from TestTable
         where ParentLevel = 0
         union all
             select
                 t.labelID,
                 t.Title,
                 t.ParentLevel,
                 [treePath] + '/' + cast(t.Title as varchar(255))
             from
                 cte
             join TestTablet on cte.labelID = t.ParentLevel
         )
     select  
         labelID,
         Title,
         ParentLevel,
         [treePath]
     from cte 
     order by treePath

Answer 1

• 我们在这里所做的只是在 CTE 中为工会的第一部分添加 lvl 0
• 然后在每次递归发生时将其递增 1（在全部并集之后）
• 然后将 WHERE 子句添加到 SELECT 以消除超过 2 的级别。

虽然我觉得这很奇怪，因为 t 在您的代码中没有别名.......

;with cte as
     (
     select 
         labelID, 
         Title, 
         ParentLevel, 
         cast(Title as varchar(max)) as [treePath],
         0 as lvl
         from TestTable
         where ParentLevel = 0
         union all
             select
                 t.labelID,
                 t.Title,
                 t.ParentLevel,
                 [treePath] + '/' + cast(t.Title as varchar(255)),
                 cte.lvl+1 as lvl
             from
                 cte
             join TestTablet t on cte.labelID = t.ParentLevel
         )
     select  
         labelID,
         Title,
         ParentLevel,
         [treePath]
     from cte 
     where lvl <=2
     order by treePath