日期:2014-05-16 浏览次数:20752 次
SELECT tjnum,REGEXP_SUBSTR(czwt,'[^#]+',1,L) FROM table1,(SELECT LEVEL L FROM dual CONNECT BY LEVEL <= 100) t1 WHERE (LENGTH(czwt)-LENGTH(REPLACE(czwt,'#','')))/3+1 >= L ORDER BY tjnum,L; --测试 [SYS@orcl] SQL>WITH table1 AS( 2 SELECT 2 id,'00411042500001' tjnum,'轻度脂肪肝###急性咽峡炎###甘油三酯高###尿酸高###谷酰转肽酶 高' czwt FROM dual UNION ALL 3 SELECT 3 id,'00311042500013' tjnum,'高血压###高尿酸血症###高脂血症###咽炎' czwt FROM dual UNION ALL 4 SELECT 4 id,'00111042500037' tjnum,'左肾错构瘤' czwt FROM dual UNION ALL 5 SELECT 5 id,'00711042500007' tjnum,'房室传导阻滞:' czwt FROM dual UNION ALL 6 SELECT 6 id,'00911042500004' tjnum,'基本正常' czwt FROM dual UNION ALL 7 SELECT 7 id,'00111042500039' tjnum,'体检正常' czwt FROM dual UNION ALL 8 SELECT 8 id,'00911042500005' tjnum,'慢性咽炎' czwt FROM dual UNION ALL 9 SELECT 9 id,'00111042500040' tjnum,'体检正常' czwt FROM dual UNION ALL 10 SELECT 10 id,'00711042500009' tjnum,'碱性磷酸酶偏低:###胆固醇(CHOL)偏高:###血红蛋白浓度(HGB)偏低:' czwt FROM dual UNION ALL 11 SELECT 11 id,'00111042600015' tjnum,'甲状腺轻度大' czwt FROM dual UNION ALL 12 SELECT 12 id,'00711042600013' tjnum,'球蛋白偏高:###直接胆红素偏高:###甘油三脂(TG)偏高:' czwt FROM dual UNION ALL 13 SELECT 13 id,'00411042600003' tjnum,'轻度脂肪肝###脾大###慢性咽炎###甲状腺功能低下###白蛋白高###球蛋白低' FROM dual 14 )SELECT tjnum,REGEXP_SUBSTR(czwt,'[^#]+',1,L) 15 FROM table1,(SELECT LEVEL L FROM dual CONNECT BY LEVEL <= 100) t1 16 WHERE (LENGTH(czwt)-LENGTH(REPLACE(czwt,'#','')))/3+1 >= L 17 ORDER BY tjnum,L; TJNUM REGEXP_SUBSTR(CZWT,'[^#]+',1,L -------------- ------------------------------ 00111042500037 左肾错构瘤 00111042500039 体检正常 00111042500040 体检正常 00111042600015 甲状腺轻度大 00311042500013 高血压 00311042500013 高尿酸血症 00311042500013 高脂血症 00311042500013 咽炎 00411042500001 轻度脂肪肝 00411042500001 急性咽峡炎 00411042500001 甘油三酯高 00411042500001 尿酸高 00411042500001 谷酰转肽酶 高 00411042600003 轻度脂肪肝 00411042600003 脾大 00411042600003 慢性咽炎 00411042600003 甲状腺功能低下 00411042600003 白蛋白高 00411042600003 球蛋白低 00711042500007 房室传导阻滞: 00711042500009 碱性磷酸酶偏低: 00711042500009 胆固醇(CHOL)偏高: 00711042500009 血红蛋白浓度(HGB)偏低: 00711042600013 球蛋白偏高: 00711042600013 直接胆红素偏高: 00711042600013 甘油三脂(TG)偏高: 00911042500004 基本正常 00911042500005 慢性咽炎 已选择28行。
------解决方案--------------------
WITH table1 AS( SELECT 2 id,'00411042500001' tjnum,'轻度脂肪肝###急性咽峡炎###甘油三酯高###尿酸高###谷酰转肽酶 高' czwt FROM dual UNION ALL SELECT 3 id,'00311042500013' tjnum,'高血压###高尿酸血症###高脂血症###咽炎' czwt FROM dual UNION ALL SELECT 4 id,'00111042500037' tjnum,'左肾错构瘤' czwt FROM dual UNION ALL SELECT 5 id,'00711042500007' tjnum,'房室传导阻滞:' czwt FROM dual UNION ALL SELECT 6 id,'00911042500004' tjnum,'基本正常' czwt FROM dual UNION ALL SELECT 7 id,'00111042500039' tjnum,'体检正常' czwt FROM dual UNION ALL SELECT 8 id,'00911042500005' tjnum,'慢性咽炎' czwt FROM dual UNION ALL SELECT 9 id,'00111042500040' tjnum,'体检正常' czwt FROM dual UNION ALL SELECT 10 id,'00711042500009' tjnum,'碱性磷酸酶偏低:###胆固醇(CHOL)偏高:###血红蛋白浓度(HGB)偏低:' czwt FROM dual UNION ALL SELECT 11 id,'00111042600015' tjnum,'甲状腺轻度大' czwt FROM dual UNION ALL SELECT 12 id,'00711042600013' tjnum,'球蛋白偏高:###直接胆红素偏高:###甘油三脂(TG)偏高:' czwt FROM dual UNION ALL SELECT 13 id,'004