sql-server - 构造此数据以便递归 cte 可以读取它？ (SQL服务器)

Question

我需要一个查询，该查询将返回给定以下信息的表的祖先。源表当前的结构如前所述，递归 CTE 不起作用。我似乎无法确定源表应该 的结构如何使 CTE 正常工作。

有人可以建议一个将返回以下内容的源表结构和查询吗？如果有比递归更好的方法，那也行。

源表包含来 self 的 SQL Server 数据库的反射(reflect)数据沿袭的信息。为了生成表 T4，您需要执行过程 P1、P2 和 P3。

规则是一个表只能有一个“父”过程，但一个过程可以建立多个表。所以P3可以构建T3和T4，但是T3只能由一个过程(P3)构建。

示例:

如果查询输入“T4”，它应该返回以下信息:

referencing_ancestor    referenced_ancestors    
P3                      T2, LOOKUP_TABLE
P2                      T1
P1                      staging

这是当前结构中的源信息，但结构可以更改。我只需要给定表的祖先信息。

declare @Dependencies table
(
    id int identity(1,1),
    referencing_name nvarchar(50) NOT NULL,
    referenced_name nvarchar(50) NULL,
    select_from int NULL,
    insert_to int NULL
) 

insert into @Dependencies
select 'P1', 'staging', 1, 0    --> P1 selects data from staging
union all
select 'P1', 'T1', 0, 1         --> P1 populates T1
union all
select 'P2', 'T1', 1, 0         --> P2 selects data from T1
union all
select 'P2', 'T2', 0, 1         --> P2 populates T2
union all
select 'P3', 'LOOKUP_TABLE', 1, 0  --> P3 selects data from LOOKUP_TABLE
union all
select 'P3', 'T2', 1, 0         --> P3 selects data from T2
union all
select 'P3', 'T3', 0, 1         --> P3 populates  T3
union all
select 'P3', 'T4', 0, 1         --> P3 populates T4 

此查询不起作用，不确定如何修复:

 ;with ancestors as 
    (
        select referencing_name, referenced_name, Level = 0
        from @Dependencies
        where referenced_name = 'T4'

        union all

        select d.referencing_name, d.referenced_name, Level + 1
        from @Dependencies d
        inner join ancestors a on a.referenced_name = d.referenced_name
        where insert_to = 0
    )

    select * from ancestors  

Answer 1

我认为您可能希望在 @Dependencies 表本身中建立某种“级别”列，但在本示例中，我只是按 DENSE_RANK 函数中的 referencing_name 进行排序。

;WITH ancestors AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY referencing_name) AS tbl_level
    FROM    @Dependencies
)
SELECT  a2.* 
FROM    ancestors a1
JOIN    ancestors a2 ON a2.tbl_level <= a1.tbl_level
WHERE   a1.referenced_name = 'T4'
AND     a2.insert_to = 0 
ORDER BY tbl_level DESC