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实现,不能用存储过程,谢谢
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实现,不能用存储过程,谢谢
解决方案 »
- 请各位高手帮忙,谢谢. 连网后提示ORA-12514.
- 请问oracle xml db repository的作用
- Oracle中如何查询第10条记录???
- oracle 8i 与 oracle 9i 在更新数据的时候,些代码时有什么区别吗? 在线等待中。。。。
- 一个比较复杂的条件查询
- 我在执行SQL语句的时候出现了问题,请大家帮忙解决,谢谢!
- ORA-00604: error occurred at recursive SQL level 2
- 如何在oralce中建立自动增加的字段
- 为什么sysdba用户无法设置事务为串行化
- oracle 还原备份问题 .bak文件
- 又是group by 的问题
- Oracle里如何解决4000字符的问题?Struts+spring+ibatis环境。
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
这种应该用存储过程或者函数来实现
原理:
读出p_aa的值
然后做个循环把P_AA值2位2位的折分,然后读出相应的p_bb的值
把p_bb的值拼起来,最后再输出结果
呵呵,贴出来很容易让人误解的
呵呵,你这句SQL语句我就写不出来,我能想到的就是写个FUNCTION去解决
看了下你写的SQL,感觉还是用FUNCTION容易看懂些