sql - 识别表中后续的父子层次结构

Question

我在表中有父子关系。我想使用父子关系表填充一个新表，该表将包含子级和层次结构中所有后续父级的顺序。

我一直在研究提供的解决方案，但无法编写为我提供所需结果的查询。

下面的代码可用于创建表和加载示例数据

create table DemoTable
(
    accountid bigint
    ,parentid bigint
    ,accountname nvarchar(128)
)
insert DemoTable(accountid,parentid,accountname)
select 1, null, 'Root'
union select 2, 1, 'Child1'
union select 3, 1, 'Child2'
union select 4, 1, 'Child3'
union select 5, 2, 'Child1.1'
union select 6, 2, 'Child1.2'
union select 7, 3, 'Child1.3'
union select 8, 7, 'Child1.3.7'
go

输出应如下所示

accountid    parentid    hierarchyLevel
8    7    3
8    3    2
8    1    1
7    3    2
7    1    1
3    1    1
6    2    2
6    1    1
2    1    1

标签： sqlsql-server

Answer 1

这可能会有所帮助....

DECLARE  @DemoTable table
(
    accountid bigint
    ,parentid bigint
    ,accountname nvarchar(128)
)
insert  @DemoTable(accountid,parentid,accountname)
select 1, null,    'Root'
union select 2, 1, 'Child1'
union select 3, 1, 'Child2'
union select 4, 1, 'Child3'
union select 5, 2, 'Child1.1'
union select 6, 2, 'Child1.2'
union select 7, 3, 'Child1.3'
union select 8, 7, 'Child1.3.7'

;WITH CTE
AS
(
SELECT accountid,parentid--,LEN(REPLACE(REPLACE(accountname,'.',''),'Child','')) hierarchyLevel
FROM @DemoTable
WHERE accountname <> 'Root'

UNION ALL

SELECT c.accountid ,d.parentid--,LEN(REPLACE(REPLACE(accountname,'.',''),'Child','')) hierarchyLevel
FROM @DemoTable d 
INNER JOIN CTE c ON d.accountid = c.parentid
WHERE d.accountname <> 'Root'

)
SELECT *,ROW_NUMBER() OVER( PARTITION  BY accountid  ORDER BY accountid ASC ,parentid ASC) hierarchyLevel
FROM CTE
order by accountid DESC ,parentid DESC --, CAST(hierarchyLevel as INT) DESC
option (maxrecursion 0)