sql - 获取顶级 parent ID 继承给 SQL 中的所有 child

Question

我有一张这样的 table

ID    Name     Parent    Code
----------------------------------
1     Item1     NULL     K123
2     Item2     NULL     K324
3     Item3      1       NULL
4     Item4      2       NULL
5     Item5      3       NULL
6     Item6      5       NULL
7     Item7      4       NULL
8     Item8      NULL    K567
9     Item9      8       NULL
10    Item10     NULL    NULL
---------------------------------

我需要像这样从其父级继承代码给所有子级

ID    Name     Parent    Code
----------------------------------
1     Item1     NULL     K123
2     Item2     NULL     K324
3     Item3      1       K123
4     Item4      2       K324
5     Item5      3       K123
6     Item6      5       K123
7     Item7      4       K324
8     Item8      NULL    K567
9     Item9      8       K567
10    Item10     NULL    NULL
---------------------------------

我尝试了下面的 CTE

;with CTE(ID,Name,Parent,Code,[Level]) as
(
    select ID,Name,Parent,Code,0 as [Level] from tbl_mytable 
    union all
    select T.ID,T.Name,T.Parent,T.Code,C.[Level]+1 from tbl_mytable T
    inner join CTE C on C.Parent=T.ID
)
select * from CTE C

这无助于我实现我正在尝试的目标。我只是 CTE 的初学者，我认为它无法解决。

Answer 1

您在正确的轨道上使用递归 CTE，但您的逻辑有点偏离。考虑这个工作版本:

WITH cte AS (
    SELECT * FROM tbl_mytable WHERE Parent IS NULL
    UNION ALL
    SELECT t1.ID, t1.Name, t1.Parent, COALESCE(t1.Code, t2.Code)
    FROM tbl_mytable t1
    INNER JOIN cte t2
        ON t1.Parent = t2.ID
)

SELECT *
FROM cte
ORDER BY ID;

Demo

递归 CTE 的基本案例组件(即联合之前的内容)应该是那些没有父项的记录。这就是我们知道他们是最高父级的方式。然后，我们通过将给定级别与紧接其上的父级匹配来递归地加入 CTE。然后，我们使用一个技巧从顶层降低代码:

COALESCE(t1.Code, t2.Code)

这将从父代码之一开始，然后向下传播到各个级别的子代码。