在有序的行列表中轉換層次結構

我試圖製作一個表示項目層次結構的簡單列表。

Tags have simple structure like
  [Id]
 ,[Name]
 ,[ParentTagId]

我嘗試製作以下結果表： ID, ParentID, Name, Lev, LevelDynamic,RowNumber LevelDyanmic用於渲染。它包含水平變化的增量。返回行的順序很重要。

WITH cte (ParentID, ID, Name, lev, shift)
             AS(SELECT   ParentTagID,
                           ID,
                           Name,
                           1,
                           ID * 10000
                  FROM     dbo.Tag
                  where ParentTagID is null
                  UNION ALL
                  SELECT   e.ParentTagId,
                           e.ID,
                           e.Name,
                           lev + 1,
                           shift + lev * 100 + 1
                  FROM     cte
                           INNER JOIN dbo.Tag AS e ON e.ParentTagId = cte.ID
                )
                 select cte.ID,
                    cte.ParentID,
                 cte.Name,
                 cte.lev,
                 cte.shift,
                 CAST(cte.lev - (LAG(cte.lev, 1, 0) OVER(ORDER BY cte.shift)) as int) as LevelDynamic,
                 CAST(ROW_NUMBER() OVER(ORDER BY cte.shift) as int) AS RowNumber
                 from CTE
                order by shift asc

但是該查詢返回的結果不正確，兄弟元素的順序不正確，這與不正確的移位計算有關。你能幫我嗎？

可能您需要深度優先的樹節點

with tag as (
   select * from (values
    (1,'.Net',cast(null as int)),
    (2,'EF',1),
    (3,'NHib',1),
    (4,'CF',2),
    (5,'Java',null),
    (6,'JRE',3)) t([Id],[Name],[ParentTagId])
),
cte (ParentID, ID, Name, lev, shift)AS(
 SELECT   ParentTagID,
               ID,
               Name,
               1,
               row_number() over(order by id)
      FROM     Tag
      where ParentTagID is null
      UNION ALL
      SELECT   e.ParentTagId,
               e.ID,
               e.Name,
               lev + 1,
               shift * 100 + row_number() over(order by e.id)
      FROM     cte
      INNER JOIN Tag AS e ON e.ParentTagId = cte.ID
)
select *,
  CAST(cte.lev - LAG(cte.lev, 1, 0) OVER(ORDER BY cast(shift as varchar(50))) as int) as LevelDynamic,
  CAST(ROW_NUMBER() OVER(ORDER BY cast(shift as varchar(50))) as int) AS RowNumber   
from cte
order by cast(shift as varchar(50))

引用自：https://dba.stackexchange.com/questions/141011