创建一个没有 rootrecord 的递归 CTE [英] Creating a recursive CTE with no rootrecord

问题描述

我对令人震惊的标题表示歉意，我试图描述性但不确定我是否抓住了重点.希望下面能解释一下

My Apologies for the appalling Title, I was trying to be descriptive but not sure I got to the point. Hopefully the below will explain it

我从一个包含以下信息的表格开始

I begin with a table that has the following information

Party Id    Party Name    Party Code   Parent Id
1           Acme 1        ACME1        1
2           Acme 2        ACME2        1
3           Acme 3        ACME3        3
4           Acme 4        ACME4        4
5           Acme 5        ACME5        4
6           Acme 6        ACME6        6

正如您所看到的，这对于递归 CTE 来说并不完美，因为它不是在没有父记录的情况下使用 NULL，而是以其自身为父级(参见第 1,3 和 6 行).然而，有些人是正常的父母.

As you can see this isn't perfect for a recursive CTE because rather than having a NULL where there isn't a parent record it is instead parented to itself (see rows 1,3 and 6). Some however are parented normally.

因此，我尝试在 CTE 中修改此表，然后将该 CTE 的输出作为我的递归查询的一部分......这似乎运行得不是很好(还没有错误)所以我想知道是否我设法创建了一个无限循环或其他一些错误，这些错误只会使查询变慢而不是终止它

I have therefore tried to amend this table in a CTE then refer to the output of that CTE as part of my recursive query... This doesn't appear to be running very well (no errors yet) so I wonder if I have managed to create an infinite loop or some other error that just slows the query to a crawl rather than killing it

我的代码在下面...请把它分开！

My Code is below... please pick it apart!

--This is my attempt to 'clean' the data and set records parented to themselves as the 'anchor' 
--record
WITH Parties
AS
(Select CASE
    WHEN Cur_Parent_Id = Party_Id THEN NULL
    ELSE Cur_Parent_Id
END AS Act_Parent_Id
, Party_Id
, CUR_PARTY_CODE
, CUR_PARTY_NAME
FROM EDW..TBDIMD_PARTIES
WHERE CUR_FLG = 1),

--In this CTE I referred to my 'clean' records from above and then traverse through them
--looking at the actual parent record identified
linkedParties
AS
(
Select Act_Parent_Id, Party_Id, CUR_PARTY_CODE, CUR_PARTY_NAME, 0 AS LEVEL
FROM Parties
WHERE Act_Parent_Id IS NULL

UNION ALL

Select p.Act_Parent_Id, p.Party_Id, p.CUR_PARTY_CODE, p.CUR_PARTY_NAME, Level + 1
FROM Parties p
inner join
linkedParties t on p.Act_Parent_Id = t.Party_Id
)

Select *
FROM linkedParties
Order By Level

根据我之前提供的数据，我期望的结果是；

From the data I supplied earlier the results I would expect are;

Party Id    Party Name    Party Code   Parent Id    Level
1           Acme 1        ACME1        1            0
3           Acme 3        ACME3        3            0
4           Acme 4        ACME4        4            0
6           Acme 6        ACME6        6            0
2           Acme 2        ACME2        1            1
5           Acme 5        ACME5        4            1

如果一切似乎没问题，那么我会假设它只是一个处理问题并开始调查，但我对 CTE 并不完全满意，所以希望在查看其他地方之前确保错误不是我的.

If everything seems to be OK then I'll assume its just a processing issue and start investigating that but I am not entirely comfortable with CTE's so wish to make sure the error is not mine before looking elsewhere.

非常感谢

推荐答案

我认为你让它变得比它需要的更复杂:).

I think that you made it more complicated than it needs to be :).

drop table #temp
GO
select 
    *
into #temp
from (
    select '1','Acme 1','ACME1','1' union all
    select '2','Acme 2','ACME2','1' union all
    select '3','Acme 3','ACME3','3' union all
    select '4','Acme 4','ACME4','4' union all
    select '5','Acme 5','ACME5','4' union all
    select '6','Acme 6','ACME6','6'
) x ([Party Id],[Party Name],[Party Code],[Parent Id])
GO

;with cte as (
    select
        *, 
        [Level] = 0
    from #temp
    where 1=1
        and [Party Id]=[Parent Id] --assuming these are root records

    union all 

    select
        t.*,
        [Level] = c.[Level]+1
    from #temp t
    join cte c
        on t.[Parent Id]=c.[Party Id]
    where 1=1
        and t.[Party Id]<>t.[Parent Id]  --prevent matching root records with themselves creating infinite recursion
)
select
    *
from cte

(* 当然应该用实际的列名替换)

(* should ofcourse be replaced with actual column names)

这篇关于创建一个没有 rootrecord 的递归 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！