关于oracle 拆分字符串加列转行的问题

来源：互联网发布：linux安装ttf字体编辑：程序博客网时间：2024/05/13 04:28

我有这样一张表VVV 类似


JPCZZH_ID                JPCZZH_CH_NDJSNRJGM
JPCZZH0000000004[{jpczzhJsYear:'2013',jpczzhChNdjsnrjgm:'22'},{jpczzhJsYear:'121',jpczzhChNdjsnrjgm:'22'}]
我想查询得到这样的结果

 JPCZZH_IDJPCZZHJSYEAR1JPCZZHCHNDJSNRJGM1JPCZZHJSYEAR2JPCZZHCHNDJSNRJGM21JPCZZH0000000004'2013''22''121''22'

也就是说把JPCZZH_CH_NDJSNRJGM里的内容都拆成字段。冒号前面的是字段名(字段名要依次加123的)，后面是值，大括号括的有N组内容，就拆出来N乘以3个字段来。

下面是我的解决办法

先建一个视图

CREATE OR REPLACE VIEW V_SZH$NDJSNRJGM ASSELECT RN,       jpczzh_id,       P1,       SUBSTR(CV0, 0, INSTR(CV0, ':', 1, 1) - 1) || P1 A,       SUBSTR(CV0, INSTR(CV0, ':', 1, 1) + 1) B  FROM (SELECT T.RN,T.jpczzh_id,T.P1,               SUBSTR(T.CA,                      INSTR(T.CA, ',', 1, C.LV) + 1,                      INSTR(T.CA, ',', 1, C.LV + 1) - (INSTR(T.CA, ',', 1, C.LV) + 1)) AS CV0          FROM (SELECT RESULT1.RN RN,P1,jpczzh_id,',' || CV1 || ',' AS CA,                       LENGTH(CV1 || ',') - NVL(LENGTH(REGEXP_REPLACE(CV1, ',', '')), 0) AS CNT                  FROM (SELECT T1.RN,T1.jpczzh_id,C1.LV AS P1,                               SUBSTR(T1.CA,                                      INSTR(T1.CA, '},', 1, C1.LV) + 2,                                      INSTR(T1.CA, '},', 1, C1.LV + 1) -                                      (INSTR(T1.CA, '},', 1, C1.LV) + 2)) AS CV1                          FROM (SELECT ROWNUM RN,jpczzh_id,                                       '},' || REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm ,'\[{?|{|}\]','') || '},' AS CA,                                       (LENGTH(REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm,'\[{?|{|}\]','') || '},') -                                       NVL(LENGTH(REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm,'\[{?|{|},|}\]','')),0)) / 2 AS CNT                                  FROM jnjp_project_czzh) T1,                               (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) C1                         WHERE C1.LV <= T1.CNT) RESULT1) T,               (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) C         WHERE C.LV <= T.CNT         ORDER BY jpczzh_id, P1, LV);

出来的结果是这样的：

   RNJPCZZH_ID               P1 A                            B11JPCZZH00000000041jpczzhJsYear1            '2013'21JPCZZH00000000041jpczzhChNdjsnrjgm1    '22'31JPCZZH00000000042jpczzhJsYear2           '121'41JPCZZH00000000042jpczzhChNdjsnrjgm2   '22'

然后再查询：

SELECT *    FROM VVV  t  LEFT JOIN (SELECT jpczzh_id ID,                    MAX(DECODE(A, 'jpczzhJsYear1', B)) JPCZZHJSYEAR1,                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm1', B)) JPCZZHCHNDJSNRJGM1,                    MAX(DECODE(A, 'jpczzhJsYear2', B)) JPCZZHJSYEAR2,                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm2', B)) JPCZZHCHNDJSNRJGM2,                    MAX(DECODE(A, 'jpczzhJsYear3', B)) JPCZZHJSYEAR3,                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm3', B)) JPCZZHCHNDJSNRJGM3,                    MAX(DECODE(A, 'jpczzhJsYear4', B)) JPCZZHJSYEAR4,                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm4', B)) JPCZZHCHNDJSNRJGM4,                    MAX(DECODE(A, 'jpczzhJsYear5', B)) JPCZZHJSYEAR5,                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm5', B)) JPCZZHCHNDJSNRJGM5               FROM V_SZH$NDJSNRJGM              GROUP BY jpczzh_id) ndjsnrjgm    ON ndjsnrjgm.id = t.jpczzh_id

得出结果：


 JPCZZH_IDJPCZZHJSYEAR1JPCZZHCHNDJSNRJGM1JPCZZHJSYEAR2JPCZZHCHNDJSNRJGM21JPCZZH0000000004'2013''22''121''22'

不知道还有没有好的解决办法，如果有的话请大家指教

0 0