从自引用表中获取分层数据

Question

从自引用表中获取分层数据

22

假设你有以下表格：

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}
{"item_id":5,"depth":2}

注：我正在寻找一种MySQL支持的方法。

- Emanuil Rusev

2

什么数据库和版本？递归查询是特定于供应商的，如果支持的话。 - D'Arcy Rittich

2

@RBarryYoung：这是假设他正在使用MS SQL Server。 - Andrew Sledge

正确，但递归CTE确实是标准的一部分，而且SQL Server并不是唯一支持它们的产品。 - RBarryYoung

1

Emmanuil：如果你需要MySQL特定的答案，那么你应该在某个地方指明。 - RBarryYoung

@RBarryYoung：对此我深表歉意。我错误地假设答案适用于任何DBMS。 - Emanuil Rusev

5个回答

6

在mysql网站上有一篇关于mysql中层次数据的好技术文章：管理mysql中的层次数据 - 你可以在那里找到几个详细的解决方案及其优缺点。

特别是“嵌套集模型”和“查找节点深度”的部分应该对您有帮助。

- FrankS

如果有人想知道Doctrine是否支持此功能，有一个不错的扩展：https://github.com/Atlantic18/DoctrineExtensions/blob/master/doc/tree.md - murtho

3

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的项，并选择这些节点的所有子节点以及它们在树中的深度。

- Aaron

我不知道Oracle有这个功能。这是一个好消息。如果在item_parent列中父级有一个null值，那么我们就可以避免使用“not in”和额外的select语句，这样不是更高效吗？ - jett

2

MySQL

这些链接提供了与MySQL相关的IT技术内容，其中包括管理分层数据的文章和MySQL书籍的一部分。

- Andrew Sledge

很遗憾，http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html 链接已失效。 - You Help Me Help You

0

我需要找到一个解决相同任务的方案，找到了一些文章，但还没有决定要走哪条路...

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

也许这些链接可以帮到你。如果你找到了好的解决方案，请在这里发布。我不能发布超过1个链接，我会在下一个帖子中添加一些链接。

- user296355

http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html - user296355

http://www.alandelevie.com/2008/07/12/recursion-less-storage-of-hierarchical-data-in-a-relational-database/ - user296355

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andrew · Accepted Answer

如果数据库是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

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