使用 SQL 定位所有可达节点
假设一个表有两列：From 和To。示例：
From To
1 2
2 3
2 4
4 5
我想知道使用 SQL 查询定位可从节点访问的所有节点的最有效方法。示例：给定 1，它将返回 2、3、4 和 5。可以使用由 UNION 子句联合的多个查询，但这会限制可以达到的级别数。也许不同的数据结构会让问题更容易处理，但这就是可用的。
我正在使用 Firebird，但我想要一个仅使用标准 SQL 的解决方案。
Suppose a table with two columns: From and To. Example:
From To
1 2
2 3
2 4
4 5
I would like to know the most effective way to locate all nodes that are reachable from a node using a SQL Query. Example: given 1 it would return 2,3,4 and 5. It is possible to use several queries united by UNION clauses but it would limit the number of levels that can be reached. Perhaps a different data structure would make the problem more tractable but this is what is available.
I am using Firebird but I would like have a solution that only uses standard SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您使用大多数品牌的数据库，则可以使用递归公用表表达式 -- 除了 MySQL 和 SQLite 以及其他一些晦涩难懂的（抱歉，我确实认为 Firebird 晦涩难懂）。此语法是 ANSI SQL 标准，但 Firebird
尚不支持它。更正： Firebird 2.1 确实支持递归 CTE，如 @Hugues Van Landeghem 评论的那样。
否则，请参阅我的演示文稿使用 SQL 的分层数据模型了解几种不同的方法。
例如，您可以为树中的每个路径存储附加行，而不仅仅是直接父/子路径。我将这种设计称为“Closure Table”。
现在您可以查询 SELECT * FROM MyTable WHERE From = 1 并获取该节点的所有后代。
PS：我会避免将列命名为
From，因为这是 SQL 保留字。You can use a recursive common table expression if you use most brands of database -- except for MySQL and SQLite and a few other obscure ones (sorry, I do consider Firebird obscure). This syntax is ANSI SQL standard, but Firebird
doesn't support it yet.Correction: Firebird 2.1 does support recursive CTE's, as @Hugues Van Landeghem comments.
Otherwise see my presentation Models for Hierarchical Data with SQL for several different approaches.
For example, you could store additional rows for every path in your tree, not just the immediate parent/child paths. I call this design Closure Table.
Now you can query
SELECT * FROM MyTable WHERE From = 1and get all the descendants of that node.PS: I'd avoid naming a column
From, because that's an SQL reserved word.不幸的是，没有一个好的通用解决方案可以适用于所有数据库的所有情况。
我建议您查看以下 MySQL 解决方案资源：
对于 PostgreSQL 和 SQL Server，您应该查看递归 CTE。
如果您使用的是 Oracle，您应该查看 CONNECT BY 这是一个SQL 的专有扩展使处理树结构变得更加容易。
Unfortunately there isn't a good generic solution to this that will work for all situations on all databases.
I recommend that you look at these resources for a MySQL solution:
For PostgreSQL and SQL Server you should take a look at recursive CTEs.
If you are using Oracle you should look at CONNECT BY which is a proprietary extension to SQL that makes dealing with tree structures much easier.
对于标准 SQL，存储具有可接受的读取性能的树的唯一方法是使用诸如路径枚举之类的 hack。请注意，这对于写入来说非常繁重。
With standard SQL the only way to store a tree with acceptable read performance is by using a hack such as path enumeration. Note that this is very heavy on writes.