请问不用递归如何用一条sql查询树节点的所有嫡系祖先或所有嫡系后代

Question

考虑如下表结构:
组织机构表organization:id,name
组织机构关系表organization_relation:id,idparent
因为是矩阵机构模型，多对多关系，所以用了关系表。
如何用一条sql语句查询出某机构的所有上级机构？包括父亲机构和爷爷老爷爷机构
请高手帮忙。

Answer 1

要求不要用oracle特殊的sql语法，不要用存储过程，只使用标准sql语法

Answer 2

http://community.csdn.net/Expert/topic/5018/5018120.xml?temp=.2339289

Answer 3

select id,name,idparent,sys_connect_by_path(name,'/') path
from(
select a.id,a.name,b.idparent
  from organization a
  join organization_relation b
  on(a.id=b.id))
start with idparent is null
connect by prior id=idparent;

Answer 4

多谢Eric_1999(╙@^@╜) ( ) 兄回复，最好用标准sql语句，因为项目是数据库类型无关的。
测试环境搭建如下：
create table ORGANIZATION
(
  ID   VARCHAR2(10) not null,
  NAME VARCHAR2(30)
);
create table ORGANIZATION_RELATION
(
  ID       VARCHAR2(10),
  IDPARENT VARCHAR2(10)
);
insert into ORGANIZATION (ID, NAME)
values ('111', '京北方');
insert into ORGANIZATION (ID, NAME)
values ('211', '研发中心');
insert into ORGANIZATION (ID, NAME)
values ('311', '基础产品部');
insert into ORGANIZATION (ID, NAME)
values ('511', '建行项目组');
insert into ORGANIZATION (ID, NAME)
values ('312', '应用产品部');
insert into ORGANIZATION_RELATION (ID, IDPARENT)
values ('211', '111');
insert into ORGANIZATION_RELATION (ID, IDPARENT)
values ('311', '211');
insert into ORGANIZATION_RELATION (ID, IDPARENT)
values ('312', '211');
insert into ORGANIZATION_RELATION (ID, IDPARENT)
values ('511', '311');
insert into ORGANIZATION_RELATION (ID, IDPARENT)
values ('511', '312');

Answer 5

多谢zcs_1(生生不息) ( ) 兄，我测试了您的代码，完全正确，可是怕sys_connect_by_path connect这些语法在其它数据库没有替代实现：）

Answer 6

select ou.*
  from organization ou,
       (select our.*
          from organization ou, organization_relation our
         where ou.id in ('511')
           and ou.id = our.id) parrel
 where ou.id = parrel.idparent
unionselect ou.*
  from organization ou,
       (select our.*
          from organization ou, organization_relation our
         where ou.id in
               (select ou.id
                  from organization ou,
                       (select our.*
                          from organization ou, organization_relation our
                         where ou.id in ('511')
                           and ou.id = our.id) parrel
                 where ou.id = parrel.idparent)
           and ou.id = our.id) parrel
 where ou.id = parrel.idparent
这样可以查询出父亲和爷爷，但是如果级别更多，则嵌套和union会更多，有没有兄台能写出高明的语法，在一条sql语句里写出递归效果，替换oracle的树查询。我说的不用递归是不用递归函数，减少函数和网络开销，如果能在一条sql语句里写出递归效果当然是好的

Answer 7

可观！ 建议你们修改一下你们的设计，这样可以提高效率，否则用复杂无比的sql，带来的是及其低下的效率，如果这个是很多人使用的。

Answer 8

to (如果你没有那么多的选择) :
我是贴主的同事，多对多的关系是不能改变的，后代查询也是不能改变的，跨数据库也是不能改变的，好象改变设计的余地不大了，可能只有参考hibernate，书写各种数据库类型的查询方言了。

Answer 9

不考虑极端的情况，通常组织机构有个10几个层次就是顶天了。 所有，可以用一个字段存储下所有祖先的信息，例如varchar2(300),其中可以用 '.'隔开。 我的设想是这样的：
id   name    Nodecode
1    a       1
2    b       1.2
3    c       1.3
4    d       1.2.4
5    e       1.2.5
6    f       1.2.5.6   从6可以直接看到它的上级脉络是6.5.2.1  。当然这里考虑的是级别不多的。但是除非你是很极端的应用，我想还是可以满足的。在插入和更新的时候注意维护nodecode即可。
   如果要照到祖先，则只要解析一下nodecode即可。
   同理如果要照后代，例如2的后代，则马山可以通过Like的方式获得.