层次/树-获取所有字符串的根

Question

假设我有下面这个表T，它通过存储父/子对来定义/存储一个树结构。这些值都是整数。假设在另一个表S中，我将每个ID/值映射到一个字符串。

例如，在S中我们有:

 Table S
 ID     Name 
 90     "node 90"
 301    "node 301"  
 etc. (even though the real names are different) 

是否有可能在T中添加一个计算列，该列为每个子节点提供路径的文本表示，一直到树的根，以附加形式，例如

"节点1>节点2>节点3"(用于子节点/叶节点3)

或

"节点10>节点20"(用于子节点/叶节点20)

如果不能通过计算列实现，那么我可以使用常规列并对该列进行一次更新吗?我在考虑一些递归的CTE，但我无法理解它(目前)。

Table T   
ParentEventID   ChildEventID
90  301
90  302
90  303
90  304
90  305
90  306
90  307
301 401
301 402
302 403
302 404
302 405
302 406
302 407
303 408
304 409
304 410
304 411
304 412
304 413
304 414
305 415
305 416
305 417
305 418
306 419
306 420
306 421
306 422
307 423
307 424
307 425
307 426
307 427
403 501
403 502
403 503
403 504
403 505
404 506
404 507
404 508
404 509
404 510
405 511
405 512
405 513
405 514
405 515
406 516
406 517
406 518
406 519
406 520
407 521
407 522
407 523
407 524
407 525
415 526
415 527
415 528
415 529
415 530
416 531
416 532
416 533
416 534
416 535
417 536
417 537
417 538
417 539
417 540
418 541
418 542
418 543
418 544
418 545
420 546
420 547
420 548
420 549
420 550
421 551
421 552
421 553
421 554
421 555
422 556
422 557
422 558
422 559
422 560

Answer 1

我是这么想的:

WITH cte AS (
    SELECT * FROM (VALUES
        (90, 301),
        (90, 302),
        (90, 303),
        (90, 304),
        (90, 305),
        (90, 306),
        (90, 307),
        (301,401),
        (301,402),
        (302,403),
        (302,404),
        (302,405),
        (302,406),
        (302,407),
        (303,408),
        (304,409),
        (304,410),
        (304,411),
        (304,412),
        (304,413),
        (304,414),
        (305,415),
        (305,416),
        (305,417),
        (305,418),
        (306,419),
        (306,420),
        (306,421),
        (306,422),
        (307,423),
        (307,424),
        (307,425),
        (307,426),
        (307,427),
        (403,501),
        (403,502),
        (403,503),
        (403,504),
        (403,505),
        (404,506),
        (404,507),
        (404,508),
        (404,509),
        (404,510),
        (405,511),
        (405,512),
        (405,513),
        (405,514),
        (405,515),
        (406,516),
        (406,517),
        (406,518),
        (406,519),
        (406,520),
        (407,521),
        (407,522),
        (407,523),
        (407,524),
        (407,525),
        (415,526),
        (415,527),
        (415,528),
        (415,529),
        (415,530),
        (416,531),
        (416,532),
        (416,533),
        (416,534),
        (416,535),
        (417,536),
        (417,537),
        (417,538),
        (417,539),
        (417,540),
        (418,541),
        (418,542),
        (418,543),
        (418,544),
        (418,545),
        (420,546),
        (420,547),
        (420,548),
        (420,549),
        (420,550),
        (421,551),
        (421,552),
        (421,553),
        (421,554),
        (421,555),
        (422,556),
        (422,557),
        (422,558),
        (422,559),
        (422,560)
    ) AS x(ParentEventID, ChildEventID)
), rcte AS (
    SELECT DISTINCT NULL AS [ParentEventID], a.[ParentEventID] AS ChildEventID, CONCAT('/', CAST(a.[ParentEventID] AS NVARCHAR(MAX)), '/') AS h
    FROM cte AS a
    WHERE NOT EXISTS (
        SELECT *
        FROM [cte]
        WHERE [cte].[ChildEventID] = a.[ParentEventID]
    )
    UNION ALL
    SELECT child.[ParentEventID], child.[ChildEventID], CONCAT(parent.h, [child].[ChildEventID], '/')
    FROM [cte] AS child
    JOIN rcte AS parent
        ON child.[ParentEventID] = [parent].[ChildEventID]
)
SELECT * FROM rcte

第一个cte只是一个快速的方式，我暴露你的数据;该解决方案的真正核心在rcte。注意，如果要查找h列，则h列可以立即转换为HierarchyID。顺便说一下，您应该查找它，因为它允许您很容易地回答"这一行的子行是什么?"或"这一行的谱系中有哪些行?"之类的问题(即无需动态计算整个层次结构)。