SQL递归逻辑

Question

我遇到了这样的情况:我需要配置现有客户端数据来解决我们的应用程序在应该正确更新表中的 ID 时却没有正确更新的问题。

这是场景。我们有一个父表，可以在其中插入有效替换现有行的行；替换可以是递归的。我们还有一个子表，它有一个指向父表的字段。在现有数据中，子表可能指向已被替换的行，我需要更正这一点。但是，我不能简单地将每一行更新为替换行，因为该行也可能已被替换，并且我需要反射(reflect)最新的行。

我试图找到一种方法来编写 CTE 来为我完成此任务，但我正在努力寻找一个查询来找到我真正想要的东西。这是我正在使用的表格的示例； “ShouldBe”列是我希望更新查询最终得到的列，考虑到某些行的递归替换。

DECLARE @parent TABLE (SampleID int, 
                   SampleIDReplace int,
                   GroupID char(1))

INSERT INTO @parent (SampleID, SampleIDReplace, GroupID)
VALUES (1, -1, 'A'), (2, 1, 'A'), (3, -1, 'A'), 
       (4, -1, 'A'), (5, 4, 'A'), (6, 5, 'A'),
       (7, -1, 'B'), (8, 7, 'B'), (9, 8, 'B')


DECLARE @child TABLE (ChildID int, ParentID int)
INSERT INTO @child (ChildID, ParentID)
VALUES (1, 4), (2, 7), (3, 1), (4, 3)

应用更新脚本后子表中的所需结果:

ChildID     ParentID    ParentID_ShouldBe
1           4           6 (4 replaced by 5, 5 replaced by 6)
2           7           9 (7 replaced by 8, 8 replaced by 9)
3           1           2 (1 replaced by 2)
4           3           3 (unchanged, never replaced)

Answer 1

以下返回您要查找的内容:

with cte as (
    select sampleid, sampleidreplace, 1 as num
    from @parent
    where sampleidreplace <> -1
    union all
    select p.sampleid, cte.sampleidreplace, cte.num+1
    from @parent p join
         cte
         on p.sampleidreplace = cte.sampleId
)
select c.*, coalesce(p.sampleid, c.parentid)
from @child c left outer join
     (select ROW_NUMBER() over (partition by sampleidreplace order by num desc) as seqnum, *
      from cte
     ) p
     on c.ParentID = p.SampleIDReplace and p.seqnum = 1

递归部分跟踪每个对应关系 (4-->5, 4-->6)。加法数是“世代”计数。我们实际上想要最后一代。这是通过使用 row_number() 函数来识别的，按 num 降序排序 - 因此 p.seqnum = 1。