SQL-检测父子关系中的循环 [英] SQL - detecting loops in parent child relations

问题描述

我在excel中有父子数据，该子数据已加载到运行MS SQL Server的第3方系统中.数据表示有向(希望)非循环图.第三方意味着我在架构中没有完全自由的手. excel数据是其他文件的串联，并且存在以下可能性:在各个文件之间的交叉引用中，有人引起了循环-即X是Y的子级(X-> Y)，然后是其他地方(Y-> A- > BX).我可以在Excel或SQL Server数据库上编写vb，vba等. excel文件几乎是3万行，因此随着数据的增长，我担心组合爆炸.因此，诸如创建具有所有路径的表之类的一些技术可能非常笨拙.我正在考虑简单地编写一个程序，该程序针对每个根对每片叶子进行树遍历，并且如果深度大于某些标称值，则会对其进行标记.
欢迎提供更好的建议或指向之前的讨论的指针.

I have parent child data in excel which gets loaded into a 3rd party system running MS SQL server. The data represents a directed (hopefully) acyclic graph. 3rd party means I don't have a completely free hand in the schema. The excel data is a concatenation of other files and the possibility exists that in the cross-references between the various files someone has caused a loop - i.e. X is a child of Y (X->Y) then elsewhere (Y->A->B-X). I can write vb, vba etc on the excel or on the SQL server db. The excel file is almost 30k rows so I'm worried about a combinatorial explosion as the data is set to grow. So some of the techniques like creating a table with all the paths might be pretty unwieldy. I'm thinking of simply writing a program that, for each root, does a tree traversal to each leaf and if the depth gets greater than some nominal value flags it.
Better suggestions or pointers to previous discussion welcomed.

推荐答案

您可以使用递归CTE来检测循环:

You can use a recursive CTE to detect loops:

with prev as (
    select RowId, 1 AS GenerationsRemoved
    from YourTable
    union all
    select RowId, prev.GenerationsRemoved + 1
    from prev
    inner join YourTable on prev.RowId = ParentRowId
    and prev.GenerationsRemoved < 55
)
select * 
from prev
where GenerationsRemoved > 50

这确实需要您指定最大递归级别:在这种情况下，CTE会运行到55，并且它将选择的子行超过50个的错误行.

This does require you to specify a maximum recursion level: in this case the CTE runs to 55, and it selects as erroneous rows with more than 50 children.

这篇关于SQL-检测父子关系中的循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！