使用递归CTE遍历父/子树？

Question

使用递归CTE遍历父/子树？

sqlsql-serverrecursioncommon-table-expression

3

我卡在了一个公共表达式（CTE）上，我想查询第一个父级为空的结果。每个子节点都是前一个父节点的子节点，而下一个节点则是当前节点的父节点，以此类推。

WITH RESULT (PARENT,CHILD,TNAME,LEVEL)
AS
(
--anchor
 SELECT E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,E.NAME ,0 AS LEVEL
  FROM RPT_SYN_M_GENERAL AS E 
 WHERE E.PARENT_GENERAL_KEY IS NULL
 UNION ALL
 --outer
 SELECT e.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,e.NAME ,LEVEL +1
 FROM RPT_SYN_M_GENERAL AS E
 INNER JOIN RESULT AS D  
 ON E.PARENT_GENERAL_KEY=D.CHILD 
)
SELECT PARENT,CHILD,TNAME,LEVEL FROM RESULT as d
order by PARENT,CHILD

上述是我的查询。请帮我迭代最后一个子元素。

我得到的输出如下:

 PARENT CHILD   TNAME               LEVEL
  NULL  0       ACCOUNT_MASTER        0
   0    1       LIABILITIES           1
   0    2       ASSETS                1
   0    3       INCOME                1
   0    4       EXPENSE               1
   0    15003   POLISHED DIAMOND      1
   0    15004   DEMO                  1
   0    15005   DEMO ( FACTORY        1
   1    238     CAPITAL A/C.          2
   1    1067    PROVISION             2
   1    1284    SECURED LOANS         2 
   1    2968    UNSECURED LOANS       2
   1    3535    SHARE HOLDERS         2
   2    484     FIXED ASSETS A/C      2
   2    3301    INVESTMENTS           2
   2    4858    CURRENT ASSETS        2
   2    4859    LOANS ADVANCES        2
   3    867     OTHER INCOME          2
   3    1246    SALES                 2

我期望得到的输出是：

 PARENT      CHILD  TNAME             LEVEL
   null      0                          0
    0        1                          1 
    1        238                        2
   238       982                        3
   982       26                         4
   982       894                        4        
   982       1321                       4
   238       7757                       3
   7757      7521                       4

- Amita

看起来你需要发布 RPT_SYN_M_GENERAL 的样例。 - John Dewey

请您尝试进行演示并按照自己的方式操作。它应该具有父键、子键和级别。 - Amita

请提供您输入表格的样本。 - Peter

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sarin · Answer 1

对于您指定的输出，您的CTE中的行需要进行排名，然后在每次迭代中仅筛选排名为1的行。

WITH RESULT (ROW_RANK, PARENT,CHILD,TNAME,[LEVEL])
AS
(
--anchor
 SELECT RANK() OVER (ORDER BY E.M_GENERAL_KEY), E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,E.NAME ,0 AS [LEVEL]
  FROM RPT_SYN_M_GENERAL AS E 
 WHERE E.PARENT_GENERAL_KEY IS NULL
 UNION ALL
 --outer
 SELECT RANK() OVER (ORDER BY E.M_GENERAL_KEY), E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,E.NAME, D.[LEVEL] +1 
 FROM RPT_SYN_M_GENERAL AS E
 INNER JOIN RESULT AS D  
 ON E.PARENT_GENERAL_KEY=D.CHILD AND ROW_RANK = 1
)
SELECT PARENT,CHILD,TNAME,LEVEL FROM RESULT as d
WHERE ROW_RANK = 1
order by PARENT,CHILD

- John Dewey · Answer 2

没有看到您的底层表，我无法测试任何建议。但是，请尝试在外部查询中切换父项和子项，看看会发生什么：

WITH RESULT (PARENT,CHILD,TNAME,LEVEL)
AS
(
--anchor
 SELECT E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,E.NAME ,0 AS LEVEL
  FROM RPT_SYN_M_GENERAL AS E 
 WHERE E.PARENT_GENERAL_KEY IS NULL
 UNION ALL
 --outer
 SELECT D.CHILD,D.PARENT,e.NAME ,LEVEL +1 --switched parent/child
 FROM RPT_SYN_M_GENERAL AS E
 INNER JOIN RESULT AS D  
 ON E.PARENT_GENERAL_KEY=D.CHILD 
WHERE LEVEL<100
)
SELECT PARENT,CHILD,TNAME,LEVEL FROM RESULT as d
order by PARENT,CHILD