oracle中使用SQL递归语句

例子

pid id

a b

a c

a e

b b

b b

c c

e e

e e

d d

指定pid=a选出

a b

a c

a e

b b

b b

c c

e e

e e

SQL语句select parentchild from test start with pid=a

connect by prior id=pid

Oracle SQL的递归查询

表机构

SQL> desc comm_org_subjection

Name Null? Type

ORG_SUBJECTION_ID NOT NULL VARCHAR() 子键

ORG_ID NOT NULL VARCHAR()

FATHER_ORG_ID NOT NULL VARCHAR() 父键

LOCKED_IF NOT NULL VARCHAR()

START_DATE NOT NULL DATE

END_DATE DATE

EDITION_NAMEPLATE NUMBER()

CODE_AFFORD_IF VARCHAR()

CODE_AFFORD_ORG_ID NOT NULL VARCHAR()

CODING_SHOW_ID NUMBER()

BSFLAG VARCHAR()

MODIFI_DATE DATE

CREATOR_ID VARCHAR()

CREATE_DATE DATE

CREATOR VARCHAR()

递归查找父结点 org_id为C下的所有子结点:

select * from comm_org_subjection a

start with _id=C

connect by prior _subjection_id=afather_org_id

递归查找子结点 org_id为C下的所有父结点:

select org_id from comm_org_subjection a

start with _id=C

connect by prior afather_org_id=_subjection_id