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进行排序~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1172679/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1172679/