Oracle的sys_connect_by_path

    偶尔在工作中也会遇到版本过于低的oracle,也偶尔会遇到某些?DBA给业务组创建账户的时候没有赋予wm_concat(column)函数相关视图的权限,然后不让你用这个函数进行字符串的拼接,在这种大前提下,有非常巧合的不让你在应用层去拼接字符串(为神马我能遇到这么巧合的事情??)

    好吧,在机缘巧合之下,还是老老实实用sql解决吧。
    想到字符串拼接,很自然就会考虑oracle的层次查询,不是start with...connect by,然后输出路径就好了吗?
    对的,一般来说,这么想是没什么问题,一层一层connect,然后输出路径嘛,可是巧合又出现了!原始数据的实例如下:
    A      B
---------------
    1     我
    1     们
    1     一
    1     起
    1     在
    1     吧
    2     我
    2     不
    2     认
    2     识
    2     你

    期望结果如下:(不要在意字的顺序╮(╯_╰)╭)
    A     B
------------------
    1     起,我,在,吧,们,一
    2     识,认,我,你,不

    从常规的层次查询方法来考虑,按照A分组,然后把 A = prior A作为条件,连接B不就好了么?
   好的,那试试看~
点击(此处)折叠或打开
  1. SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B
  2. FROM test
  3. START WITH a = 1
  4. CONNECT by A = PRIOR A
  5. GROUP BY A;
    执行结果如下:
    ORA-01436:用户数据中的CONNECT BY 循环
    仔细看的话,CONNECT by A = PRIOR A这个地方,A=1的话,确实会产生无限循环。
    所以连接的条件肯定要改,这个时候,伪列的作用就出来了~
    对A进行分组,然后构造伪列,让原始数据的每一行都有一个不同的值,然后再进行连接,这样就可以避免无限循环的出现
    先构造伪列,看看效果~

点击(此处)折叠或打开

  1. SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN FROM TEST
    结果如下:
    B    A    RN
-------------------
    起   1     1
    我   1     2
    在   1     3
    吧   1     4
    们   1     5
    一   1     6
    识   2     1
    认   2     2
    我   2     3
    你   2     4
    不   2     5

    从结果可以看出,RN列如预期一般,在每个分组内产生了顺序序列
    那么结合之前的查询语句,把这个结果表作为之前查询的原始表:

点击(此处)折叠或打开

  1. SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, \',\')), \',\') B
  2. FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
  3.           FROM TEST)
  4. START WITH RN = 1
  5. CONNECT BY RN - 1 = PRIOR RN
  6. GROUP BY A;

    看看结果:
    A    B
------------
    1    起,认,我,吧,们,一
    2    起,认,我,吧,不

    可以看出来,貌似A=1和A=2的内容混在一起了,虽然这几个字的拼接确实是按照1->6拼接的,但是拼接顺序变成了A11->A22->A23->A14->A15->A16
    这时候,需求是A=1的内容要和A=1的内容拼接,不能出现这种混合的情况,那么可以考虑在connect的时候添加一个限制条件,让A=1的内容和A=1的拼接,
    对SQL语句进行修改:

点击(此处)折叠或打开

  1. SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, \',\')), \',\') B
  2. FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
  3.           FROM TEST)
  4. START WITH RN = 1
  5. CONNECT BY RN - 1 = PRIOR RN
  6.        AND A = PRIOR A
  7. GROUP BY A;
    结果如下
    A     B
------------------
    1     起,我,在,吧,们,一
    2     识,认,我,你,不

   需求达成,收工~
   PS:事实上伪列确实是非常好用,尤其是对需要拼接的字符串有特殊的排序要求,那么在生成伪列的查询里面,添加order进行排序~

上一篇:【Android 安全】DEX 加密 ( Proguard 混淆 | 将混淆后的报错信息转为原始报错信息 | retrace.bat 命令执行目录 | 暴露更少信息 )(二)


下一篇:执行exec dbms_logmnr_d.build('Logminer.ora','file directory'),提示下标超界