sql - parent 、子女、子子女 CTE

Question

这是我为获取父帐户、子帐户和子帐户而创建的 CTE，但它在顶部显示了 4 个父帐户。

with AccountCTE(Id,Code,AccountName,[Level])
as
(
  select D.Id, D.Code, D.AccountName, 1
  from DefaultAccount D
  join AccountClass on D.AccountClassId = AccountClass.Id
  where D.ParentAccountId Is Null
  union All
  select Da.Id, Da.Code, Da.AccountName, AccountCTE.Level +1
  from DefaultAccount Da
  join AccountCTE on Da.ParentAccountId = AccountCTE.Id
)
select * from AccountCTE

Answer 1

尝试将此作为您的解决方案...

这里 tab 是表名 Id 是你的 rowId 而 parentid 是你的 parentId。

  ;WITH CTE AS (
      SELECT Id, ParentId,0 AS [Level], CAST(Id AS varchar(1000)) AS Heirarchy,Id AS TopParentId
      FROM dbo.tab
      WHERE Id IN (SELECT Id FROM tab WHERE ParentId IS NULL)
      UNION ALL

      SELECT mgr.Id, mgr.ParentId, CTE.[Level] +1 AS [Level], 
            CAST(( CAST(mgr.Id AS VARCHAR(1000)) + '>' + CTE.Heirarchy) AS varchar(1000)) AS Heirarchy, CTE.TopParentId
      FROM CTE
        INNER JOIN dbo.tab AS mgr
          ON CTE.Id = mgr.ParentId
    )
    select * from cte order by heirarchy