我卡在了一个公共表达式(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