如何在MySQL中找到最古老的“祖先”,其中每行都有一个父级或空值?

p3rjfoxz  于 2022-10-22  发布在  Mysql
关注(0)|答案(3)|浏览(164)

我有一组这样的数据:

ID      NAME        PARENT
----    ------      -------
1       Obj #1      NULL
2       Obj #2      1
3       Obj #3      4
4       Obj #4      2
5       Obj #5      3
6       Obj #6      NULL
7       Obj #7      6

所以,如果我想让他们和他们最古老的祖先在一起,我会得到这样的结果:

ID      NAME        OLDEST
----    ------      -------
1       Obj #1      NULL
2       Obj #2      1
3       Obj #3      1
4       Obj #4      1
5       Obj #5      1
6       Obj #6      NULL
7       Obj #7      6

我该如何查询才能做到这一点?

bz4sfanl

bz4sfanl1#

由于时间不够,我将只为您提供一个解决方案(这不是最好的解决方案,但它符合使用重复自连接的逻辑,并且只有在您知道层次结构树的上限的情况下)。
这是一个有效的SQL Fiddle

测试数据生成(与您的一样,没有“name”列):

create table tbl(id int, parent int);
insert into tbl values (1,null),(2,1),(3,4),(4,2),(5,3),(6,null),(7,6);

查询以在层次结构树中找到深度为5的最老祖先:

select
  t1.id, coalesce(t5.id, t4.id, t3.id, t2.id) as oldest
from tbl t1
left join tbl t2 on t1.parent = t2.id
left join tbl t3 on t2.parent = t3.id
left join tbl t4 on t3.parent = t4.id
left join tbl t5 on t4.parent = t5.id
order by 1;
1l5u6lss

1l5u6lss2#

我通常会在MySQL中重新设计这个表,使其看起来像这样:

id      name
10      Obj#1
1010    Obj#2 -- parent = 10
101020  Obj#6 -- parent = 1010 -- grandparent = 10 ... etc

我为您当前的设计提供了一个惰性SQL:(6层嵌套,如果嵌套更深,可以添加更多)

SELECT a.id, a.NAME,
  ifnull(p6.PARENT, ifnull(p5.PARENT, ifnull(p4.PARENT, ifnull(p3.PARENT, ifnull(p2.PARENT, ifnull(p.PARENT, p.id)))))) AS PARENT
FROM 0_a2 AS a
LEFT JOIN 0_a2 AS p  ON a.PARENT  = p.id
LEFT JOIN 0_a2 AS p2 ON p.PARENT  = p2.id
LEFT JOIN 0_a2 AS p3 ON p2.PARENT = p3.id
LEFT JOIN 0_a2 AS p4 ON p3.PARENT = p4.id
LEFT JOIN 0_a2 AS p5 ON p4.PARENT = p5.id
LEFT JOIN 0_a2 AS p6 ON p5.PARENT = p6.id
ORDER BY a.id


小时

z18hc3ub

z18hc3ub3#

一些评论建议使用“嵌套集”模型,该模型在Joe Celko的SQL书中得到了普及。但我发现数据模型很难维护,修改数据也很复杂。
我更喜欢一种我称之为“闭包表”的设计,我在这个广受欢迎的Stack Overflow答案中描述了这种设计:将平面表解析为树的最有效/优雅的方法是什么?
您的查询如下所示:

SELECT c.ID, c.NAME, a.ID AS OLDEST
FROM MyTable AS t
JOIN ClosureTable AS c ON t.ID = c.descendant 
JOIN MyTable AS a ON a.ID = c.ancestor
WHERE a.PARENT IS NULL;

我还在我的演示文稿Models for Hierarchical Data中介绍了这个模型(以及其他解决方案)。
这是我演讲的视频记录:Models for Hierarchical Data
我还在我的书SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming中写了一章关于分层数据。
如果您需要在当前存储数据时使用查询,请参阅Quassnoi的聪明解决方案:https://stackoverflow.com/a/8111762/20860
但诚然,这不是一个稳定的解决方案,因为它依赖于MySQL的未记录行为。我会远离它。
如果你不能改变代码或表结构,而你今天就需要它,你就必须接受Kamil G.的答案,即使它被限制在一个固定的层次结构深度。对不起,没有其他解决办法了。

相关问题