如何在一个SQL查询中获取任意递归深度的祖先ID? [英] How can I get ancestor ids for arbitrary recursion depth in one SQL query?

问题描述

表:

    id  parent_id
    --------------
    a_1 NULL
    a_2 NULL
    b_1 a_1
    c_1 b_1
    d_1 c_1

现在，我的ID是:d_1

如何在一个SQL查询中获取a_1，b_1，c_1? (d_1的祖先ID)?

How can I get a_1,b_1,c_1 in one SQL query? (d_1's ancestor ids)?

推荐答案

我真的不认为您可以在简单的SQL语句中完成此操作，这是SQL语言的基本限制.

I really don't think you can do this in a simple SQL statement, it's a fundamental limitation of the SQL language.

此处是一个令人讨厌的存储过程，可以完成您想要的操作(对于SQL Server).因此，您需要执行以下操作.

Here is a heinous stored procedure that does roughly what you want (for SQL Server). So you would need to do something like this.

这篇关于如何在一个SQL查询中获取任意递归深度的祖先ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！