Tsql 递归查询 - 最后一个事件 Id

Question

我有两个包含这样数据的表(要创建的 T-sql 代码):

CREATE TABLE [dbo].[IdChanges](
    [ReplacedId] [int] NOT NULL,
    [OverrideId] [int] NOT NULL 
);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(17,18);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(18,19);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(19,20);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(12,13);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(13,14);

CREATE TABLE [dbo].[IdActivity](
    [Id] [int] NOT NULL,
    [IsActive] [bit] NOT NULL   
);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(14,1);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(20,1);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(17,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(18,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(19,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(12,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(13,0);
go

我如何获得(通过递归 cte 查询或其他方式)更改链中每个 ReplacedId 和 Last Override id 的结果。我希望结果完全如下所示。 5 行 ...结果可能为空

ReplacedId   LastOverrideId
17           20
18           20
19           20
12           14
13           14

Answer 1

递归 CTE 是解决此问题的方法。确保您的 IdChanges执行前不要有循环。

;WITH RecursiveActivities AS
(
    SELECT
        OriginalActivityID = I.Id,
        OverrideActivityID = I.Id,
        Level = 0
    FROM
        [dbo].[IdActivity] AS I

    UNION ALL

    SELECT
        OriginalActivityID = R.OriginalActivityID,
        OverrideActivityID = I.OverrideId,
        Level = R.Level + 1
    FROM
        RecursiveActivities AS R
        INNER JOIN [dbo].[IdChanges] AS I ON R.OverrideActivityID = I.ReplacedId
),
MaxLevelByActivity AS
(
    SELECT
        R.OriginalActivityID,
        MaxLevel = MAX(R.Level)
    FROM
        RecursiveActivities AS R
    GROUP BY
        R.OriginalActivityID
)
SELECT
    R.OriginalActivityID,
    R.OverrideActivityID
FROM
    RecursiveActivities AS R
    INNER JOIN MaxLevelByActivity AS M ON
        R.OriginalActivityID = M.OriginalActivityID AND
        R.Level = M.MaxLevel

如果您不想看到没有变化的事件，只需添加 WHERE R.OriginalActivityID <> R.OverrideActivityID在最后。