問(wèn)題描述

我正在研究采用父子關(guān)系表的遞歸查詢

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

并且假設(shè)顯示以下結(jié)果:

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

當(dāng)然部門和單位的數(shù)量更大.基本任務(wù)是顯示父級(jí)及其子級(jí)的 parentId.您對(duì)如何實(shí)現(xiàn)這一目標(biāo)有任何想法嗎?

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?

到目前為止我只做了這個(gè)查詢

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

推薦答案

您的語(yǔ)法不太正確，但思路是正確的.最后，您希望獲取父級(jí)的父級(jí)為 NULL 的行.這可能有效(未經(jīng)測(cè)試):

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;

這篇關(guān)于如何使用 T-sql 遞歸查詢?yōu)樽约汉秃⒆语@示父 ID的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！