用递归CTE遍历父/子树? [英] Walk parent/child tree with recursive CTE?
本文介绍了用递归CTE遍历父/子树?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我被cte困住了,
我想查询第一个父级为null的地方。
和先前父母的孩子,将是下一个父母,依此类推。
I am stuck with a cte, I want a query where in first parent is null. and child of pervious parent, will be the parent of next, and so on.
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
中选择父母,孩子,姓氏,结果级别为d
以上是我的查询。
请帮助我遍历最后一个孩子。
The above mentioned is my query. Please help me to iterate through last 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
推荐答案
对于您指定的输出,需要对CTE中的行进行排名,然后在每次迭代中仅筛选排名为1的行。
For the output you specify, the rows in your CTE would need to be ranked and then you just filter for rows ranked 1 in each iteration.
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
这篇关于用递归CTE遍历父/子树?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文