SQL:如何选择物化路径中的所有父节点? [英] SQL: How to select all parent nodes in Materialized path?

问题描述

MySQL数据库在实例化路径数据结构的帮助下存储树.如何选择给定节点的所有父代?

The MySQL databse stores tree with the help of materialized path data structure. How to select all parents of the given node?

我使用点'.作为路径分隔符，数据库结构包含级别"字段，该字段指示树中节点的深度.

I use dot '.' as path separator, and database structure includes 'level' field that indicates the depth of a node in the tree.

让我拥有那些树，并需要节点'a.b.d.e'的所有父级:

Lets I have those tree and need all parents of the node 'a.b.d.e':

id | path
 a | a
 b |  a.b
 c |   a.b.c
 d |   a.b.d
 e |    a.b.d.e
 f |  a.f

脚本应返回:

a
a.b
a.b.d
a.d.d.e (optional)

如何在MySQL中做到这一点?

How to do that in MySQL?

推荐答案

我在@quambo发表的问题中找到了解决方案.在这里:

I've found the solution in the question posted by @quambo. Here it is:

SELECT * FROM table
WHERE ? LIKE id||%
ORDER BY path

装有?"有必要放置到给定节点的路径.

Insted of '?' it is necessary to place the path to the given node.

SELECT * FROM table
WHERE 'a.b.d.e' LIKE id||%
ORDER BY path

这篇关于SQL:如何选择物化路径中的所有父节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！