从自引用表获取层次结构数据

Question

假设您有下表：

items(item_id, item_parent)  

...并且它是一个自引用表 - item_parent 引用 item_id。

您将使用什么 SQL 查询来选择表中的所有项目及其深度，其中项目的深度是该项目的所有父项和祖父项的总和。

如果以下是表的内容：

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

...查询应检索以下对象集：

{“item_id”：1，“深度”：0}
{"item_id":2,"深度":0}
{“item_id”：3，“深度”：1}
{“item_id”：4，“深度”：1}
{“item_id”：5，“深度”：2}

PS我正在寻找MySQL支持的方法。

Answer 1

如果数据库是 SQL 2005 / 2008 那么...

最简单的方法是使用设计为递归的 CTE（通用表表达式）。

 WITH myCTE (Item_id, Depth)
 AS
 (
    Select Item_ID, 0 as Depth From yourTable where Item_Parent=0
    Union ALL
    Select yourTable.Item_ID, Depth + 1 
    From yourTable 
    inner join myCte on yourTable.item_Parent = myCte.Item_Id
 )

 Select Item_id, Depth from myCTE

输出如下：

Item_Id  Depth
    1   0
    2   0
    3   1
    4   1
    5   2

您可以根据需要对其进行格式化。

Answer 2

Oracle 有一个非常方便的语法来检索分层数据，如下所示：

select
    item_id,
    item_parent,
    level as depth
from
    items
connect by
    prior item_id = item_parent
start with
    item_parent not in (select item_id from items)

这从树的根节点开始，作为 item_parent 不存在于表中的项目作为 item_id，并选择这些节点的所有子节点，以及它们在表中的深度。树。