在线高分求等答案

[zhoubin5460]

目前有个表：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 甘油三酯高

[yinan9]

都够牛叉的

[opps_zhou]

都是夜生活丰富的人

[BenChiM888]

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,'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

[ruyee365]

up
[Quote=引用 4 楼 opps_zhou 的回复:]
都是夜生活丰富的人
[/Quote]