SQL-检测父子关系中的循环-SQL-detectingloopsinparentchildrelations

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.

我在excel中有父子数据,它被加载到运行MS SQL服务器的第三方系统中。数据代表有向(希望)非循环图。第三方意味着我没有完全自由的模式。 excel数据是其他文件的串联,并且存在这样的可能性:在各种文件之间的交叉引用中有人引起了循环 - 即X是Y(X-> Y)的子项,然后是其他文件(Y-> A- > BX)。我可以在excel或SQL server db上编写vb,vba等。 excel文件几乎是30k行,所以当数据设置增长时,我担心组合爆炸。因此,创建包含所有路径的表的一些技术可能非常难以处理。我正在考虑简单地编写一个程序,对于每个根,它会遍历每个叶子的树,如果深度大于某个标称值,则标记它。欢迎提出更好的建议或对前一次讨论的指示。

1 个解决方案

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 prevwhere GenerationsRemoved > 50