表中的数据如下:
TEX-34423 19700101 2009030000001
TEX-34423 19700101 2009030000009
TEX-34423 19700101 2009030000010
TEX-34423 19700101 2009030000011
TEX-34423 19700101 2009030000012
TEX-34423 19700101 2009030000013
TEX-34423 19700101 2009030000014
TEX-34423 19700101 2009030000040
TEX-34423 19700101 2009030000002
TEX-34423 19700101 2009030000041
TEX-34423 19700101 2009030000042
TEX-34423 19700101 2009030000043
TEX-34423 19700101 2009030000044
TEX-34423 19700101 2009030000045
TEX-34423 19700101 2009030000046
TEX-34423 19700101 2009030000006以往得到的结果:
TEX-34423 19700101 2009030000001,2009030000009,2009030000010,2009030000011,2009030000012,2009030000013,2009030000014,2009030000040,2009030000002,2009030000041,2009030000042,2009030000043,2009030000044,2009030000045,2009030000046,2009030000006第三列被合并成一行数据了。我在网上查到用sys_connect_by_path()这个函数,
配合 start with ...
connect BY prior .....
来实现,但是效率很低,所以想问问 各位高手这种问题通常是如何做到,谢谢!
TEX-34423 19700101 2009030000001
TEX-34423 19700101 2009030000009
TEX-34423 19700101 2009030000010
TEX-34423 19700101 2009030000011
TEX-34423 19700101 2009030000012
TEX-34423 19700101 2009030000013
TEX-34423 19700101 2009030000014
TEX-34423 19700101 2009030000040
TEX-34423 19700101 2009030000002
TEX-34423 19700101 2009030000041
TEX-34423 19700101 2009030000042
TEX-34423 19700101 2009030000043
TEX-34423 19700101 2009030000044
TEX-34423 19700101 2009030000045
TEX-34423 19700101 2009030000046
TEX-34423 19700101 2009030000006以往得到的结果:
TEX-34423 19700101 2009030000001,2009030000009,2009030000010,2009030000011,2009030000012,2009030000013,2009030000014,2009030000040,2009030000002,2009030000041,2009030000042,2009030000043,2009030000044,2009030000045,2009030000046,2009030000006第三列被合并成一行数据了。我在网上查到用sys_connect_by_path()这个函数,
配合 start with ...
connect BY prior .....
来实现,但是效率很低,所以想问问 各位高手这种问题通常是如何做到,谢谢!
但是你可以模仿它写个函数。
WITH TEST AS
(
SELECT 'TEX-34423' AS str1, '19700101' AS str2,'2009030000001' AS STR3 FROM dual
union all
SELECT 'TEX-34423' AS str1, '19700101' AS str2,'2009030000009' AS STR3 FROM dual
union all
SELECT 'TEX-34423' AS str1, '19700101' AS str2,'2009030000010' AS STR3 FROM dual
)
SELECT str1, str2,LTRIM(MAX(SYS_CONNECT_BY_PATH(STR3, ',')), ',') AS STR3
FROM (SELECT str1,
str2,
str3,
ROW_NUMBER() OVER(PARTITION BY str1 ORDER BY STR3) as CURR,
ROW_NUMBER() OVER(PARTITION BY str1 ORDER BY STR3) - 1 as PREV
FROM TEST)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR
and str1 = PRIOR str1
GROUP BY str1,str2试试看。
connect BY prior .....这种方式效率低了,我试过,25条数据合并 都需要7秒多时间,所以才求别的办法