sql join使用递归cte

Question

Edit: Added another case scenario in the notes and updated the sample attachment.

我正在尝试编写一个 sql 来获取附加有此问题的输出以及示例数据。
有两个表，一个具有不同的 ID (pk) 及其当前标志。
另一个具有 Active ID(fk 到第一个表中的 pk)和 Inactive ID(fk 到第一个表中的 pk)
最终输出应返回两列，第一列包含来自第一个表的所有不同 ID，第二列应包含来自第二个表的 Active ID。
下面是sql:

IF OBJECT_ID('tempdb..#main') IS NOT NULL DROP TABLE #main;
IF OBJECT_ID('tempdb..#merges') IS NOT NULL DROP TABLE #merges
IF OBJECT_ID('tempdb..#final') IS NOT NULL DROP TABLE #final

SELECT DISTINCT id, 
       current
INTO   #main 
FROM   tb_ID t1 


--get list of all active_id and inactive_id 

SELECT DISTINCT active_id, 
            inactive_id, 
            Update_dt  
INTO   #merges 
FROM   tb_merges 
-- Combine where the id from the main table matched to the inactive_id (should return all the rows from #main)

   SELECT id, 
   active_id AS merged_to_id 
   INTO   #final 
   FROM   (SELECT t1.*, 
           t2.active_id, 
           Update_dt , 
           Row_number() 
             OVER ( 
               partition BY id, active_id 
               ORDER BY Update_dt DESC) AS rn 
    FROM   #main t1 
           LEFT JOIN #merges t2 
                  ON t1.id = t2.inactive_id) t3 
  WHERE  rn = 1 

 SELECT *
 FROM #final

这个 sql 部分有效。它不起作用，id 曾经处于事件状态然后变为非事件状态。
请注意:

事件 ID 应返回最后一个最活跃的 ID

没有任何事件 ID 的 ID 应该为空或 ID 本身

ID，其中当前 = 0，在这些情况下，事件 ID 应该是 tb_ID

中的 ID 当前

ID 可能会互换。例如有两个 ID 6 和 7，当 6 处于事件状态时 7 处于非事件状态，反之亦然。了解最新事件状态的唯一方法是通过更新日期

附上的样本可能很容易理解



看起来我可能不得不使用递归 cte 来实现结果。有人可以帮忙吗？
感谢您的时间!

Answer 1

我认为您是正确的，递归 CTE 看起来是一个很好的解决方案。我不确定我是否完全理解您的要求，尤其是关于 update_dt列，只是因为数据按原样有点抽象，但我已经尝试了一下，它似乎确实适用于您的示例数据。评论解释了正在发生的事情。

declare @tb_id table (id bigint, [current] bit);
declare @tb_merges table (active_id bigint, inactive_id bigint, update_dt datetime2);
insert @tb_id values
    -- Sample data from the question.
    (1, 1),
    (2, 1),
    (3, 1),
    (4, 1),
    (5, 0),
    -- A few additional data to illustrate a deeper search.
    (6, 1),
    (7, 1),
    (8, 1),
    (9, 1),
    (10, 1);

insert @tb_merges values
    -- Sample data from the question.
    (3, 1, '2017-01-11T13:09:00'),
    (1, 2, '2017-01-11T13:07:00'),
    (5, 4, '2013-12-31T14:37:00'),
    (4, 5, '2013-01-18T15:43:00'),
    -- A few additional data to illustrate a deeper search.
    (6, 7, getdate()),
    (7, 8, getdate()),
    (8, 9, getdate()),
    (9, 10, getdate());

if object_id('tempdb..#ValidMerge') is not null
    drop table #ValidMerge;

-- Get the subset of merge records whose active_id identifies a "current" id and
-- rank by date so we can consider only the latest merge record for each active_id.
with ValidMergeCTE as
(
    select
        M.active_id,
        M.inactive_id,
        [Priority] = row_number() over (partition by M.active_id order by M.update_dt desc)
    from 
        @tb_merges M
        inner join @tb_id I on M.active_id = I.id 
    where
        I.[current] = 1
)
select
    active_id,
    inactive_id
into
    #ValidMerge
from
    ValidMergeCTE
where
    [Priority] = 1;

 -- Here's the recursive CTE, which draws on the subset of merges identified above.
 with SearchCTE as
 (
    -- Base case: any record whose active_id is not used as an inactive_id is an endpoint.
    select
        M.active_id,
        M.inactive_id,
        Depth = 0
    from
        #ValidMerge M
    where
        not exists (select 1 from #ValidMerge M2 where M.active_id = M2.inactive_id)

    -- Recursive case: look for records whose active_id matches the inactive_id of a previously
    --                 identified record.
    union all
    select
        S.active_id,
        M.inactive_id,
        Depth = S.Depth + 1
    from
        #ValidMerge M
        inner join SearchCTE S on M.active_id = S.inactive_id
 )
 select
    I.id,
    S.active_id
 from
    @tb_id I
    left join SearchCTE S on I.id = S.inactive_id;

结果:

id      active_id
------------------
1       3
2       3
3       NULL
4       NULL
5       4
6       NULL
7       6
8       6
9       6
10      6