从自引用表中获取层次结构数据 [英] Getting hierarchy data from self-referencing tables

问题描述

假设您有下表:

items(item_id, item_parent)  

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

... and it is a self-referencing table - item_parent refers to item_id.

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

What SQL query would you use to SELECT all items in the table along with their depth where the depth of an item is the sum of all parents and grand parents of that item.

如果表的内容如下:

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

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

... the query should retrieve the following set of objects:

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

{"item_id":1,"depth":0}
{"item_id":2,"depth":0}
{"item_id":3,"depth":1}
{"item_id":4,"depth":1}
{"item_id":5,"depth":2}

P.S.我正在寻找一种MySQL支持的方法.

P.S. I'm looking for a MySQL supported approach.

推荐答案

如果数据库是SQL 2005/2008，则...

If the database is SQL 2005 / 2008 then...

最简单的方法是使用旨在递归的CTE(公用表表达式).

The easiest way to get this is using a CTE (Common Table Expression) that is designed to recurse.

 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

您可以根据需要设置其格式.

From that you can format it as you wish.

这篇关于从自引用表中获取层次结构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！