熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle中使用SQL遞歸語句

2013-11-13 22:13:28  來源: Oracle 

  例子

  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


From:http://tw.wingwit.com/Article/program/Oracle/201311/18535.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.