从代表树结构的 Oracle 表中选择所有后代行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4717435/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting all descendant rows from an Oracle table representing a tree structure
提问by ckpwong
I have a table MYTYPE in Oracle 10g representing a tree structure, which is something like this:
我在 Oracle 10g 中有一个表 MYTYPE 表示树结构，它是这样的：
ID | PARENTID | DETAIL
I would like to select all rows in MYTYPE which are descendants of a particular ID, such that I can create queries elsewhere such as:
我想选择 MYTYPE 中作为特定 ID 的后代的所有行，以便我可以在其他地方创建查询，例如：
SELECT *
FROM MYDETAIL
WHERE MYTYPEID IN [all MYTYPE which are descendants of some ID];
What is a cost-efficient way of building the descendant set, preferably without using PL/SQL?
什么是构建后代集的经济高效的方法，最好不使用 PL/SQL？
回答by OMG Ponies
Oracle didn't support the ANSI hierarchical syntax of using a recursive Subquery Factoring (CTE in SQL Server syntax) until 11g R2, so you have to use Oracle's native CONNECT BY syntax (supported since v2):
Oracle 直到 11g R2 才支持使用递归子查询因子分解的 ANSI 分层语法（SQL Server 语法中的 CTE），因此您必须使用 Oracle 的原生 CONNECT BY 语法（自 v2 起支持）：
SELECT t.*
FROM MYTABLE t
START WITH t.parentid = ?
CONNECT BY PRIOR t.id = t.parentid
Replace the question mark with the parent you want to find the hierarchical data based on.
将问号替换为要基于其查找分层数据的父项。
Reference:
参考：
回答by orangepips
Managing hierarchical data using ID,ParentIDcolumns in an RDBMS is known as the Adjacency Listmodel. While very easy to implement and maintain (i.e. insert, update, delete), it's expensive to determine lineage (i.e. ancestors and descendants). As other answers already have written, Oracle's CONNECT BYwill work, but this is an expensive operation. You may be better off representing your data differently.
使用ID,ParentIDRDBMS 中的列管理分层数据称为邻接列表模型。虽然很容易实现和维护（即插入、更新、删除），但确定世系（即祖先和后代）的成本很高。正如其他答案已经写的那样，Oracle 的CONNECT BY将工作，但这是一项昂贵的操作。您最好以不同的方式表示您的数据。
For your case, the easiest solution might adding a what's called a Hierarchy Bridgetable to your schema and adding a LEVELcolumn to your original table. The table has columns ID,DescendantIDwhereby selecting on ID gives all descendant records, and selecting by DescentantID gives all ancestor records. LEVELis necessary on the base table to order records. In this way you make a tradeoff of expensive updates for cheap reads, which is what your question implies you want.
对于您的情况，最简单的解决方案可能是向您的架构添加一个所谓的层次结构桥表，LEVEL并向您的原始表添加一列。该表有列ID,DescendantID，根据 ID 选择会给出所有后代记录，而通过 DescentantID 选择会给出所有祖先记录。LEVEL需要在基表上对记录进行排序。通过这种方式，您可以权衡昂贵的更新以换取廉价的读取，这就是您的问题所暗示的您想要的。
Other possibilities that involve changing your base data include Nested Set and Materialized Path representations. That offer similar tradeoffs of more expensive writes for much cheaper reads. For a complete list of options, pros and cons, and some implementation notes, see my previous question on the topic.
涉及更改基础数据的其他可能性包括嵌套集和实体化路径表示。这提供了更昂贵的写入与更便宜的读取的类似权衡。有关选项、优缺点以及一些实施说明的完整列表，请参阅我之前关于该主题的问题。
回答by Abhishek Maurya
Here is the details for 'connect by' features in oracle. http://psoug.org/reference/connectby.html
以下是 oracle 中“连接方式”功能的详细信息。 http://psoug.org/reference/connectby.html
回答by FrustratedWithFormsDesigner
Oracle can do recursive queries.
Try looking into start with ... connect by, something like this:
Oracle 可以进行递归查询。尝试调查start with ... connect by，像这样：
Select *
from MYDETAIL
Starting with PARENTID= 1 --or whatever the root ID is
connect by PARENTID = prior ID
