oracle中使用sys_connect_by_path进行表中行值连接

一、最原始的表说明

CREATE TABLE DATADIC
(
  DATAID       NUMBER                           NOT NULL,
  NAME         VARCHAR2(100 BYTE),
  VALUE        VARCHAR2(100 BYTE),
  PARENTID     NUMBER,
  SEQUENCE     NUMBER,
  DESCRIPTION  VARCHAR2(1000 BYTE)
)

 

 datadic 是一张树状的数据字典表，dataId：主键  value：对应的值  parentId：父节点的主键

 使用select * from datadic 查询完数据对应的值如下：

 约定：根的id为0，可以看到根下面有两条记录，分别行业数据，政务数据。

版次及其他版本形式下面有九个子节点，也就是九条根据，分别为3版、1 版、2版……增订本、2版（修订本）

要实现的最终效果是：

   parentID(父节点id)     parentValue(父节点值)    childvalue(子节点值)

        0                                    根                         行业数据*政务数据

      ……                                 ……                        ……

        140                             版次及其他版本形式    3版*1 版*2版……增订本*2版（修订本）

二、取按照parentid分组，组内部按照dataid排序后的行号

select  t.parentid, 
                t.value, 
                t.dataid,
                (row_number()  /* 按照parentid分组，组内部按照dataid排序后的行号*/
                 over(partition by parentid order by dataid)
                ) numid 
           from datadic t

查询后的记录集如下：

这个记录表示，parentId为0的记录有6条，numid就是按照parentId分组后按照dataid排序后的rownum.

 

 重点函数：row_number()   over(partition by 分组列 order by 排序列)

还有相关的函数：

      rank() over(partition by 分组列 order by 排序列)   ：和名次一样，并列2个之后是第三名

      densrank() over(partition by 分组列 order by 排序列)   :连续顺序，有2个第二名，仍然跟前第三名。

三、按照上面的行号进行轮循，进行组内每行字符串的连接。

select parentid, parentValue,
        ltrim(sys_connect_by_path(value, '*'), '*') valuues 
   from (select t.parentid, 
                   t.value, 
	   t.dataid,
	   parent.VALUE as  parentValue,
                   (row_number()  /* 按照parentid分组，组内部按照dataid排序后的行号*/
                      over(partition by t.parentid order by t.dataid)
                    ) numid 
           from bap_datadic t,bap_datadic parent
           where t.PARENTID=parent.DATAID
   ) 
   WHERE connect_by_isleaf = 1 
   start with numid = 1 
   connect by numid - 1 = prior numid    and parentid = prior parentid; 

 查询结果如下，可以看到显示父节点id、父节点值、父节点下所有子节点的值。

 

重点函数：sys_connect_by_path(value, '*')

     value表示要连接的字段，‘*’表示连接符。

    使用这个方法之前必须在where条件中构建树

    where start with 条件1  connect by prior 条件2

   条件1 ：表示起始条件，例如，起始条件为组内排序后的rownum为1。

   条件2 ：表示要连接的下一行与上一行的关系，例如上面第一记录，valuues  对应的值是：“出版行业*版权行业”。那么“版权行业”与“出版行业”之间的关系是：相同的parentId中的numid+1，所以其条件为：

        start with numid = 1
       connect by numid - 1 = prior numid    and parentid = prior parentid;

其中，prior.列名：代表上一行的列。

 

SYS_CONNECT_BY_PATH ：实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来

 

CONNECT_BY_ROOT： 它用在列名之前用于返回当前层的根节点

 

connect_by_isleaf：来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。

 

CONNECT_BY_ISCYCLE：Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。

 

 

  select temp.CASE_ID,ltrim(max(sys_connect_by_path(temp.name,'//')),'//')
  from 
  (   select asso.CASE_ID as CASE_ID,
             subject.NAME as name,
            (row_number()  over(partition by asso.CASE_ID order by asso.ID)  ) numid                       
      from IPMS_SUBJECT_CASE_ASSO asso,IPMS_SUBJECT subject
      where  asso.SUBJECT_ID=subject.ID  
  )temp
  start with temp.numid=1
  connect by temp.CASE_ID=prior temp.CASE_ID
  and temp.numid-1=prior temp.numid
  group by temp.CASE_ID

 

评论

3 楼 xunmengsj 2014-04-08  

很好很强大，写的也很明白，解决了我的问题。

2 楼 poing 2012-04-13  

不错。。。解决了我刚遇到的问题。。。。没看太懂。。收藏留着以后搞懂

1 楼 wmcoo 2011-10-18  

原创不，转了