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

Question

假设您有下表:

items(item_id, item_parent)  

...这是一个自引用表 - item_parent 引用 item_id。

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

如果表的内容如下:

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

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

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

附:我正在寻找一种支持 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

您可以根据需要格式化它。