如何使用 T-sql 递归查询为自己和孩子显示父 ID

问题描述

我正在研究采用父子关系表的递归查询

I am working on recursive query which take table with parent-child relation

ID | ParentID | description
1 | null | Company
2 | 1 | Department
3 | 2 | Unit1
4 | 2 | Unit2
5 | 4 | Unit3
6 | 4 | Unit4

并且假设显示以下结果:

and is suppose to display following result:

ID | ParentID | description
1 | null | Company
2 | 2 | Department
3 | 2 | Unit1
4 | 2 | Unit2
5 | 2 | Unit3
6 | 2 | Unit4

当然部门和单位的数量更大.基本任务是显示父级及其子级的 parentId.您对如何实现这一目标有任何想法吗?

Of course the number of Deparments and units is larger. The basic quest is to display parentId for parent and its child level. Do you have any ideas how to achive this?

到目前为止我只做了这个查询

So far I only made this query

WITH cte (ID, ParentID, description)
AS
(
    SELECT ID, ParentID, description
    FROM T1
  UNION ALL
    SELECT  e.ID, e.ParentID, e.description
    FROM T2 AS e
    JOIN cte ON e.ID = cte.ParentID

)
 SELECT 
 cte.ID, cte.ParentID, cte.description
FROM cte
cte.ParentID is not null

推荐答案

您的语法不太正确，但思路是正确的.最后，您希望获取父级的父级为 NULL 的行.这可能有效(未经测试):

Your syntax isn't quite right, but the idea is in the right direction. In the end, you want to fetch the rows where the parent's parent is NULL. This might work (it is untested):

WITH cte(ID, ParentID, description, lev) AS
      (SELECT ID, ParentID, description, 1 as lev
       FROM table T1
       UNION ALL
       SELECT  cte.ID, e.ParentID, cte.description, cte.lev + 1
       FROM table e JOIN 
            cte
            ON e.ID = cte.ParentID
     )
SELECT cte.ID, cte.ParentID, cte.description
FROM cte left outer join
     table t
     on cte.ParentId = t.ParentId     
WHERE t.ParentID is null;