我有一组这样的数据:
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
我该如何查询才能做到这一点?
3条答案
按热度按时间bz4sfanl1#
由于时间不够,我将只为您提供一个解决方案(这不是最好的解决方案,但它符合使用重复自连接的逻辑,并且只有在您知道层次结构树的上限的情况下)。
这是一个有效的SQL Fiddle。
测试数据生成(与您的一样,没有“name”列):
查询以在层次结构树中找到深度为5的最老祖先:
1l5u6lss2#
我通常会在MySQL中重新设计这个表,使其看起来像这样:
我为您当前的设计提供了一个惰性SQL:(6层嵌套,如果嵌套更深,可以添加更多)
小时
z18hc3ub3#
一些评论建议使用“嵌套集”模型,该模型在Joe Celko的SQL书中得到了普及。但我发现数据模型很难维护,修改数据也很复杂。
我更喜欢一种我称之为“闭包表”的设计,我在这个广受欢迎的Stack Overflow答案中描述了这种设计:将平面表解析为树的最有效/优雅的方法是什么?
您的查询如下所示:
我还在我的演示文稿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.的答案,即使它被限制在一个固定的层次结构深度。对不起,没有其他解决办法了。