父级，子级，和子子级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

- The_Programmer92

---

那有什么问题呢？你想要得到什么结果？ - Nick

@NIck 我想要得到类似这样的结果：负债 > 股本 > - The_Programmer92

@Nick或者我想要将结果的父级放在最上面，然后是它的子级和子子级，但是当前的查询会将所有4个父级都放在最上面。 - The_Programmer92

这可能在您的应用程序层最容易完成，因为您需要根据ParentId值对整个树进行排序。 - Nick

Answer 1

尝试将以下内容作为解决方案...

这里tab是表名，Id是您的行ID，parentid是您的父级ID。

  ;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

- DarkRob