sql中拆分字符串问题
a表中有一个字段p_aa   值为 '61,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99 '   此值为插入值,长度不定,也有可以能为 '61,81 '或其他
b表有一个字段p_bb为主键,并且关联p_aa,每条记录对应p_cc,对应关系为
            p_aa                     p_bb
            61                           0
            81                           1
            82                           2
..............................等等

现在要得到的结果是 '0,1,2,3........ '
也就是先拆分原字符串,得到每个字串后到b表找到对应的p_cc,打印出来   即可,用sql实现,不能用存储过程,谢谢

------解决方案--------------------
SQL> select distinct
2 zz.p_aa,
3 ltrim(first_value(zz.path) over(partition by zz.p_aa order by zz.lev desc), ', ')
4 from (select tt.p_aa,
5 level lev,
6 sys_connect_by_path(tt.num_value, ', ') as path
7 from (
8 select A.*,B.*,
9 A.p_aa || row_number() over(partition by A.p_aa order by A.p_aa) as left_num,
10 A.p_aa || (row_number() over(partition by A.p_aa order by A.p_aa)-1) as right_num,
11 decode(sign(instr(A.p_aa,B.p_bb)),1,B.p_cc) as num_value
12 from (
13 select '61,81,82 ' as p_aa from dual
14 union all
15 select '82,83,95,99 ' as p_aa from dual
16 )A,
17 (select 61 as p_bb, 0 as p_cc from dual
18 union all
19 select 81 as p_bb, 1 as p_cc from dual
20 union all
21 select 82 as p_bb, 2 as p_cc from dual
22 union all
23 select 83 as p_bb, 3 as p_cc from dual
24 union all
25 select 95 as p_bb, 4 as p_cc from dual
26 union all
27 select 99 as p_bb, 5 as p_cc from dual
28 )B
29 where decode(sign(instr(A.p_aa,B.p_bb)),1,B.p_cc) is not null
30 )tt
31 connect by prior tt.left_num = tt.right_num
32 )zz;

P_AA LTRIM(FIRST_VALUE(ZZ.PATH)OVER
----------- --------------------------------------------
61,81,82 0,1,2
82,83,95,99 2,3,4,5

------解决方案--------------------
这个用一句SQL不可能实现的,楼上的办法是在p_aa是给定值的前提下才能实现的,如果是个变值,刚根本不能实现的
这种应该用存储过程或者函数来实现
原理:
读出p_aa的值
然后做个循环把P_AA值2位2位的折分,然后读出相应的p_bb的值
把p_bb的值拼起来,最后再输出结果