ORACLE 如何解析号段，高手上。。。

Question

 有如下数据

写一条sql得到如下数据

 

卡号	仓库
KC0003	WW
KC0004	WW
到	WW
KC0012	WW

 

麻烦各位大哥大姐帮忙谢谢

Answer 1

[quote]
Sql代码

SQL> WITH tb AS (

2 SELECT 'KC0003' min_card_id,'KC0012' max_card_id,'ww' source_dept FROM DUAL UNION ALL

3 SELECT 'KB0006' min_card_id,'KB0010' max_card_id,'aa' source_dept FROM DUAL

4 )

5 SELECT SUBSTR(t1.min_card_id,1,2) || TO_CHAR(SUBSTR(t1.min_card_id,3) + rn - 1,'fm0999') card_id,

6 t1.source_dept

7 FROM tb t1,

8 (SELECT ROWNUM rn

9 FROM (SELECT MAX(SUBSTR(max_card_id,3) - SUBSTR(min_card_id,3)) + 1 loop_num

10 FROM tb)

11 CONNECT BY ROWNUM <= loop_num) t2

12 WHERE SUBSTR(max_card_id,3) >= SUBSTR(t1.min_card_id,3) + rn - 1

13 ORDER BY t1.source_dept,t2.rn

14 ;

CARD_ID SOURCE_DEPT

---

KB0006 aa

KB0007 aa

KB0008 aa

KB0009 aa

KB0010 aa

KC0003 ww

KC0004 ww

KC0005 ww

KC0006 ww

KC0007 ww

KC0008 ww

KC0009 ww

KC0010 ww

KC0011 ww

KC0012 ww

15 rows selected

[/quote]

改进下！

[code="sql"]
SELECT REGEXP_SUBSTR (t1.min_card_id, '[a-zA-Z]+')
|| TO_CHAR (REGEXP_SUBSTR (t1.min_card_id, '[0-9]+') + rn - 1,
'FM0999')
card_id,
t1.source_dept
FROM CARD_TEST1 t1,
( SELECT ROWNUM rn
FROM (SELECT MAX(REGEXP_SUBSTR (max_card_id, '[0-9]+')
- REGEXP_SUBSTR (min_card_id, '[0-9]+'))
+ 1
loop_num
FROM CARD_TEST1 )
CONNECT BY ROWNUM <= loop_num) t2
WHERE REGEXP_SUBSTR (max_card_id, '[0-9]+') >=
REGEXP_SUBSTR (t1.min_card_id, '[0-9]+') + rn - 1
ORDER BY t1.source_dept, t2.rn;
[/code]