目前有个表：table1 字段内容为：
ID TJNUM CZWT
2 00411042500001 轻度脂肪肝###急性咽峡炎###甘油三酯高###尿酸高###谷酰转肽酶 高
3 00311042500013 高血压###高尿酸血症###高脂血症###咽炎
4 00111042500037 左肾错构瘤
5 00711042500007 房室传导阻滞：
6 00911042500004 基本正常
7 00111042500039 体检正常
8 00911042500005 慢性咽炎
9 00111042500040 体检正常
10 00711042500009 碱性磷酸酶偏低：###胆固醇(CHOL)偏高：###血红蛋白浓度(HGB)偏低：
11 00111042600015 甲状腺轻度大
12 00711042600013 球蛋白偏高：###直接胆红素偏高：###甘油三脂(TG)偏高：
13 00411042600003 轻度脂肪肝###脾大###慢性咽炎###甲状腺功能低下###白蛋白高###球蛋白低
想查询结果以czwt值中的###来拆分字段：
例如：
00411042500001 轻度脂肪肝
00411042500001 急性咽峡炎
00411042500001 甘油三酯高
------解决方案--------------------
- SQL code
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行。
------解决方案--------------------
- SQL code
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,'00411042600003' tjnum,'轻度脂肪肝###脾大###慢性咽炎###甲状腺功能低下###白蛋白高###球蛋白低' FROM dual )SELECT tjnum,substr(czwt,l,instr(czwt||'###','###',l)-l) as czwt from table1 ,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100)WHERE substr('###'||czwt,l,3)='###'order by ID