问题描述
我正在研究采用父子关系表的递归查询
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;