复杂的sql树行 [英] complex sql tree rows

问题描述

表结构

id    |    message    |    reply_id
1     |    help me!   |    0
434   |    love to    |    1
852   |    didn't work |    434
0110  |    try this   |    852
2200  |    this wont  |    0
5465  |    done. :)   |    0110

我在树菜单的中间有一个ID"852"，但我想获取所有先前相关的行和下一相关的行，因此我想获得以下结果:

i have a id "852" which is middle of tree menu, but i want to get all the previous related and next related rows, so i want to get following results like this:

救救我！ >喜欢>没用>尝试一下>完成. :)(此结果在php循环后显示，但从启动程序ID 1开始循环，回复ID为0.

help me! > love to > didn't work > try this > done. :) (this result shows like that after php loop, but starts looping from starter id 1 with reply id 0.

注意:结果中未显示2200个ID，因为它不属于该组.

NOTE: 2200 id didn't show in the result, because its not part of the group.

推荐答案

有多种方法可以使分层信息在SQL中更易于使用:

There are several alternatives for making hierarchical information easier to work with in SQL:

• 公用表表达式(根据SQL-2003标准)支持针对您正在使用的父ID类型数据的递归SQL查询.到目前为止，MySQL不支持此功能. PostgreSQL 8.4，Microsoft SQL Server和IBM DB2是支持CTE语法的RDBMS品牌的示例. Oracle还对SQL语法进行了专有扩展，支持递归查询.

• Common Table Expressions (per the SQL-2003 standard) support recursive SQL queries against the parent-id type of data you're using. So far, MySQL does not support this feature. PostgreSQL 8.4, Microsoft SQL Server, and IBM DB2 are examples of RDBMS brands that support CTE syntax. Oracle also has a proprietary extension to SQL syntax that supports recursive queries.

嵌套集(@ phantombrain提到的左/右解决方案)是Joe Celko的书用于Smarties的SQL中的树和层次结构"中详细介绍的解决方案，以及许多文章和博客中在互联网上发布信息.

Nested Sets (the left/right solution that @phantombrain mentions) is a solution detailed in Joe Celko's book "Trees and Hierarchies in SQL for Smarties" and also in numerous articles and blog postings on the internet.

路径枚举(也称为实体化路径)在层次结构的每一行中存储一个字符串，以记录该行的祖先的路径.将此内容与LIKE查询结合使用，可以将路径字符串与其祖先的路径和后代的路径进行比较.

Path Enumeration (aka Materialized Path) stores a string in each row in the hierarchy to note the path of ancestors of that row. Combine this with LIKE queries to compare the path string to its ancestors' paths and descendants' paths.

关闭表(又称传递关闭关系)使用第二个表来存储所有祖先后代关系，而不仅仅是您所使用的设计中的直接父关系.一旦存储了所有路径，许多类型的查询将变得更加容易.

Closure Table (aka Transitive Closure Relation) uses a second table to store all ancestor-descendant relationships, not just the immediate parent as in the design you're using. Many types of queries become easier once you have all the paths stored.

混合解决方案也存在.例如，在执行操作时存储直接父ID，但也要存储树的根.现在，您可以获取同一层次结构中的所有其他行，将它们提取到应用程序代码中，并使用常规数据结构整理出树.

Hybrid solutions also exist. For example, store the immediate parent id as you're doing, but also the root of the tree. Now you can get all other rows in the same hierarchy, fetch them into application code, and sort out the tree with conventional data structures.

这篇关于复杂的sql树行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！