我有一个像

create table site
(
site_Id int(5),
parent_Id int(5),
site_desc varchar2(100)
);

字段的意义：

Significance of the fields:

要求是，如果我有一个SITE_ID作为输入，我需要所有标记的​​网站下方的标识。 例如：

The requirement is that if I have a site_id as an input, and I need all the ids tagged below the site. For Example:

                    A
                   / \
                  B   C
                / | \ /\
               D  E F G H
              /\
             I  J

所有节点都是SITE_ID。

All the nodes are the site_Id.

表中包含的数据是这样的：

The table contains data like this:

Site_id  | Parent_ID  |  site_desc
_________|____________|___________
 A       |   -1       |   
 B       |    A       |
 C       |    A       |
 D       |    B       |
 E       |    B       |
 F       |    B       |
 I       |    D       |
 J       |    D       |

......

......

A是B和C等的母公司。

A is the parent of B and C and so on.

如果B是给定那么查询需要获取D中的输入，E，I，F，J

If B is the input given then the query need to fetch D, E, I, F, J

有当前通过在一个循环中的多个查询来实现，但我想在查询中的最小数目来实现这一点。

It is currently achieved through multiple queries in a loop, but I was thinking to achieve this in a minimum number of queries.

我目前做的是::

下来表决

该算法是这样的：

Initially create a data set object which you will populate, by fetching data from the data base. 
Create a method which takes the parent id as parameter and returns its child nodes if present, and returns -1, if it doesnt have a child. 
Step1: Fetch all the rows, which doesn't have a parent(root) node. 
Step2: Iterate through this result. For example if prod1 and prod2 are the initial returned nodes, in the resultset. 
Iterating this RS we get prod1, and we insert a row in our DataSET obj. 
Then we send the id of prod1 to getCHILD method, to get its child, and then again we iterate the returned resultset, and again call the getCHILD method, till we dont get the lowest node.

我需要在我的数据模型约束的最佳优化技术。随意，如果您有什么意见或建议来回答。 请提出任何东西。 先谢谢了。

I need the best optimized technique within my data model constraint. Feel free to answer if you have any suggestion. Please suggest anything. Thanks in advance.

推荐答案

不幸的是，如果你无法改变的数据模型，并且你使用MySQL，你停留在一个情况下，你需要递归查询和您再使用不支持递归查询的数据库管理系统。

Unfortunately, if you can't change the data model, and you're using MySQL, you're stuck in a situation where you need recursive queries and you're using a DBMS that doesn't support recursive queries.

Quassnoi写了一个有趣的系列博客文章，显示技术用于查询分层数据。他的解决方案是很聪明，但很复杂。 http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

Quassnoi wrote an interesting series of blog articles, showing techniques for querying hierarchical data. His solutions are quite clever, but very complex. http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

PostgreSQL是另一种开放源代码关系数据库，它不href="http://www.postgresql.org/docs/9.2/static/queries-with.html">支持递归查询