代码之家 › 专栏 › 技术社区 › F.B. ten Kate

递归获取所有引用实体的查询

common-table-expression recursion tsql sql-server sql

F.B. ten Kate · 技术社区 · 7 年前

我有一个数据模型,它由“Claims”组成,其中(为了简化stackoverflow),只有一个OpenAmount字段。还有另外两个表,“ClaimCoupling”和“ClaimEntryReference”。

为了简单起见,我删除了所有金额,因为这不是我目前正在努力解决的问题。

我曾尝试用递归CTE来实现这一点,但这(正确地)在循环引用中失败了。我想我应该用一个简单的where语句来解决这个问题,在这个语句中,我只会说递归地添加尚未属于CTE的记录,但这是不允许的。。。。

    WITH coupledClaims AS (
    --Get all unique combinations 
    SELECT cc.SubstractedFromClaim_ID AS Claim_ID,
           cc.AddedToClaim_ID AS Linked_Claim_ID FROM dbo.ClaimCoupling cc
    UNION
    SELECT cc.AddedToClaim_ID AS Claim_ID,
           cc.SubstractedFromClaim_ID AS Linked_Claim_ID FROM dbo.ClaimCoupling cc
),
MyClaims as
(
  SELECT * FROM Claim WHERE OpenAmount <> 0
  UNION ALL
  SELECT c.* FROM coupledClaims JOIN MyClaims mc ON coupledClaims.claim_id = mc.ID JOIN claim c ON c.ID = coupledClaims.linked_Claim_ID
  WHERE c.ID NOT IN (SELECT ID FROM MyClaims)
)
SELECT * FROM MyClaims

在花了太长时间摆弄这个问题之后,我决定用一个实际的循环来做…@Rowcount,然后简单地手动将它们添加到一个表变量中,但当我编写这个解决方案(我确信我可以开始工作)时,我想我首先问一下这里,因为我不喜欢在TSQL中编写循环,因为我总是觉得它很难看,效率很低。

http://sqlfiddle.com/#!6/129ad5/7/0

我希望这里的人能有一个很好的方法来处理这个问题(很可能我在递归CTE上做错了什么)。为了完成此操作,请在MS SQL 2016上完成。

1 回复 | 直到 4 年前

Community CDub 4 年前

这是我到目前为止所学到和做的。感谢的评论 habo 这是指以下问题; Infinite loop in CTE when parsing self-referencing table

首先,我决定至少“解决”我的问题,并编写了一些手动递归,这解决了我的问题,但不如我希望/认为更容易阅读和执行手动递归解决方案的CTE解决方案“漂亮”。

/****************************/
/* CLAIMS AND PAYMENT LOGIC */
/****************************/
DECLARE @rows as INT = 0
DECLARE @relevantClaimIds as Table(
Debtor_ID INT,
Claim_ID int
)
SET NOCOUNT ON

--Get anchor condition
INSERT INTO @relevantClaimIds (Debtor_ID, Claim_ID)
select Debtor_ID, ID
from Claim c
WHERE OpenAmount <> 0

--Do recursion
WHILE @rows <> (SELECT COUNT(*) FROM @relevantClaimIds)
BEGIN
set @rows = (SELECT COUNT(*) FROM @relevantClaimIds)

--Subtracted
INSERT @relevantClaimIds (Debtor_ID, Claim_ID)
SELECT DISTINCT c.Debtor_ID, c.id
FROM claim c
inner join claimcoupling cc on cc.SubstractedFromClaim_ID = c.ID
JOIN @relevantClaimIds rci on rci.Claim_ID = cc.AddedToClaim_ID
--might be multiple paths to this recursion so eliminate duplicates
left join @relevantClaimIds dup on dup.Claim_ID = c.id
WHERE dup.Claim_ID is null

--Added
INSERT @relevantClaimIds (Debtor_ID, Claim_ID)
SELECT DISTINCT c.Debtor_ID, c.id
FROM claim c
inner join claimcoupling cc on cc.AddedToClaim_ID = c.ID
JOIN @relevantClaimIds rci on rci.Claim_ID = cc.SubstractedFromClaim_ID
--might be multiple paths to this recursion so eliminate duplicates
left join @relevantClaimIds dup on dup.Claim_ID = c.id
WHERE dup.Claim_ID is null

--Payments
INSERT @relevantClaimIds (Debtor_ID, Claim_ID)
SELECT DISTINCT c.Debtor_ID, c.id
FROM @relevantClaimIds f
join ClaimEntryReference cer on f.Claim_ID = cer.Claim_ID
JOIN ClaimEntryReference cer_linked on cer.ClaimEntry_ID = cer_linked.ClaimEntry_ID AND cer.ID <> cer_linked.ID
JOIN Claim c on c.ID = cer_linked.Claim_ID
--might be multiple paths to this recursion so eliminate duplicates
left join @relevantClaimIds dup on dup.Claim_ID = c.id
WHERE dup.Claim_ID is null
END

然后,在我收到并阅读了评论后,我决定尝试一下这样的CTE解决方案;

CTE递归

with Tree as
        (
        select Debtor_ID, ID AS Claim_ID, CAST(ID AS VARCHAR(MAX)) AS levels
        from Claim c
        WHERE OpenAmount <> 0

        UNION ALL
        SELECT c.Debtor_ID, c.id, t.levels + ',' + CAST(c.ID AS VARCHAR(MAX)) AS levels
        FROM claim c
        inner join claimcoupling cc on cc.SubstractedFromClaim_ID = c.ID
        JOIN Tree t on t.Claim_ID = cc.AddedToClaim_ID
        WHERE (','+T.levels+',' not like '%,'+cast(c.ID as varchar(max))+',%')

        UNION ALL
        SELECT c.Debtor_ID, c.id, t.levels + ',' + CAST(c.ID AS VARCHAR(MAX)) AS levels
        FROM claim c
        inner join claimcoupling cc on cc.AddedToClaim_ID = c.ID
        JOIN Tree t on t.Claim_ID = cc.SubstractedFromClaim_ID
        WHERE (','+T.levels+',' not like '%,'+cast(c.ID as varchar(max))+',%')

        UNION ALL
        SELECT c.Debtor_ID, c.id, t.levels + ',' + CAST(c.ID AS VARCHAR(MAX)) AS levels
        FROM Tree t
        join ClaimEntryReference cer on t.Claim_ID = cer.Claim_ID
        JOIN ClaimEntryReference cer_linked on cer.ClaimEntry_ID = cer_linked.ClaimEntry_ID AND cer.ID <> cer_linked.ID
        JOIN Claim c on c.ID = cer_linked.Claim_ID
        WHERE (','+T.levels+',' not like '%,'+cast(c.ID as varchar(max))+',%')
        )
select  DISTINCT Tree.Debtor_ID, Tree.Claim_ID
from Tree

这种解决方案确实“短”得多,对眼睛更容易,但它实际上表现更好吗?

性能差异

手册CPU 16,读取1793,持续时间13

热膨胀系数;CPU 47,读取4001,持续时间48

结论

不确定这是由于CTE解决方案中需要的varchar转换,还是在完成递归之前必须进行一次额外的迭代,但它实际上需要比手动递归更多的资源。