虚心请教字符串合并

显示全部楼层 · 2009-2-13 09:38:53

prm_arg1 varchar(500) :=’200801;200802;200804;200602;200604;200708‘;
prm_arg2 varchar(500) :=‘200803;200802;200804;200601;200604;200709’;
prm_arg3 varchar(500) :=’200803;200802;200804;200601;200604;200710‘;

--将上述字符串合并为如下效果：
prm_res :='200601;200602;200604;200708;200709;200710;200801;200802;200803;200804';
prm_arr :='011;100;111;100;010;001;100;111;011;111';
首先将三个数组合并并排序，同时有个辅助字符串来标识prm_res中每一项在prm_arg1,prm_arg2,prm_arg3,中是否出现，例如011表明没有在第一个出现，其他类似
请教高手有没有好点的解决方法或者思想，望点播下
[ 本帖最后由 bluestar0817 于 2009-2-25 09:00 编辑 ]

千问 · 2009-2-13 09:38:53

先合并起来列转行排序去重复后定位辅助字符串最后在行转列

千问 · 2009-2-13 09:38:53

先定标识位,然后在排序,去重复!

千问 · 2009-2-13 09:38:53

转行,去重,再合并即可
[ 本帖最后由 8193102 于 2009-2-25 12:38 编辑 ]

千问 · 2009-2-13 09:38:53

WITH TEMP AS
(
SELECT '200801;200802;200804;200602;200604;200708' STR FROM DUAL
UNION
SELECT '200803;200802;200804;200601;200604;200709' STR FROM DUAL
UNION
SELECT '200803;200802;200804;200601;200604;200710' STR FROM DUAL
)
SELECT WMSYS.WM_CONCAT(STR1) STR,WMSYS.WM_CONCAT(ISHAV) ISHAV FROM
(
SELECT STR1,REPLACE(WMSYS.WM_CONCAT(ISHAV),',','') ISHAV FROM
(SELECT T2.STR STR1,T1.STR STR2,CASE WHEN INSTR(T1.STR,T2.STR,1)>0 THEN 1 ELSE 0 END ISHAV
FROM (SELECT STR,ROWNUM RN FROM TEMP) T1,
(SELECT * FROM
(SELECT DISTINCT(REGEXP_SUBSTR(T1.STR,'[0-9]+',1,T2.LEV)) STR FROM
(SELECT REPLACE(WMSYS.WM_CONCAT(STR),',',';')||';' STR FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL <=

(SELECT LENGTH(STR)-LENGTH(REPLACE(STR,';','')) RN FROM

(SELECT REPLACE(WMSYS.WM_CONCAT(STR),',',';')||';' STR FROM TEMP)

)
) T2
) ORDER BY STR
) T2
ORDER BY T2.STR,T1.RN
) GROUP BY STR1
)
200601,200602,200604,200708,200709,200710,200801,200802,200803,200804
011,100,111,100,001,001,100,111,011,111

虚心请教 字符串合并

虚心请教字符串合并