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

最新推荐文章于 2024-06-14 15:21:56 发布

qingfeng_825

最新推荐文章于 2024-06-14 15:21:56 发布

阅读量185

点赞数

分类专栏：数据库文章标签： Oracle 出版

本文链接：https://blog.csdn.net/qingfeng_825/article/details/83847544

版权

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

一、最原始的表说明

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