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

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，“ depth”：0}
{“ item_id”：2，“ depth”：0}
{“ item_id”：3，“ depth”：1}
{“ item_id”：4，“ depth”： 1}

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

由此，您可以根据需要设置其格式。