好吧,在机缘巧合之下,还是老老实实用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不就好了么?
好的,那试试看~
点击(此处)折叠或打开
-
SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B
-
FROM test
-
START WITH a = 1
-
CONNECT by A = PRIOR A
- GROUP BY A;
ORA-01436:用户数据中的CONNECT BY 循环
仔细看的话,CONNECT by A = PRIOR A这个地方,A=1的话,确实会产生无限循环。
所以连接的条件肯定要改,这个时候,伪列的作用就出来了~
对A进行分组,然后构造伪列,让原始数据的每一行都有一个不同的值,然后再进行连接,这样就可以避免无限循环的出现
先构造伪列,看看效果~
点击(此处)折叠或打开
- 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列如预期一般,在每个分组内产生了顺序序列
那么结合之前的查询语句,把这个结果表作为之前查询的原始表:
点击(此处)折叠或打开
-
SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, \',\')), \',\') B
-
FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
-
FROM TEST)
-
START WITH RN = 1
-
CONNECT BY RN - 1 = PRIOR RN
- 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语句进行修改:
点击(此处)折叠或打开
-
SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, \',\')), \',\') B
-
FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
-
FROM TEST)
-
START WITH RN = 1
-
CONNECT BY RN - 1 = PRIOR RN
-
AND A = PRIOR A
- GROUP BY A;
A B
------------------
1 起,我,在,吧,们,一
2 识,认,我,你,不
需求达成,收工~
PS:事实上伪列确实是非常好用,尤其是对需要拼接的字符串有特殊的排序要求,那么在生成伪列的查询里面,添加order进行排序~