关于字符串解析与递归算法的问题

Question

数据库表wbs

wbs_id wbs_code parent_wbs_id

0 NRG00870
1 SI 0 （wbs_code_path = NRG00870.SI）
2 SW 0 （wbs_code_path = NRG00870.SW）
3 SI 1 （wbs_code_path = NRG00870.SI.SI）
4 SI 3 （wbs_code_path = NRG00870.SI.SI.SI）

wbs_code 可以重复

假定给定字符串wbs_code全路径，现在要求出其对应的wbs_id，请问该怎么写

如 String wbs_code_path = "NRG00870.SI" 求出其对应的 wbs_id 为 1
wbs_code_path = "NRG00870.SI.SI" 求出其对应的 wbs_id 为 3
wbs_code_path = "NRG00870.SI.SI.SI" 求出其对应的 wbs_id 为 4

Answer 1

[code="sql"]
SQL> WITH t AS (
2 SELECT 0 wbs_id,'NRG00870' wbs_code,'' parent_wbs_id FROM DUAL UNION ALL
3 SELECT 1 wbs_id,'SI' wbs_code,'0' parent_wbs_id FROM DUAL UNION ALL
4 SELECT 2 wbs_id,'SW' wbs_code,'0' parent_wbs_id FROM DUAL UNION ALL
5 SELECT 3 wbs_id,'SI' wbs_code,'1' parent_wbs_id FROM DUAL UNION ALL
6 SELECT 4 wbs_id,'SI' wbs_code,'3' parent_wbs_id FROM DUAL
7 )
8 SELECT * FROM t
9 SELECT m.* FROM (
10 SELECT t.*,SUBSTR(SYS_CONNECT_BY_PATH(t.wbs_code,'.'),2) wbs_path FROM t
11 START WITH t.parent_wbs_id IS NULL
12 CONNECT BY PRIOR t.wbs_id = t.parent_wbs_id
13 ) m
14 WHERE m.wbs_path = 'NRG00870.SI'
15 ;

SQL> WITH t AS (
2 SELECT 0 wbs_id,'NRG00870' wbs_code,'' parent_wbs_id FROM DUAL UNION ALL
3 SELECT 1 wbs_id,'SI' wbs_code,'0' parent_wbs_id FROM DUAL UNION ALL
4 SELECT 2 wbs_id,'SW' wbs_code,'0' parent_wbs_id FROM DUAL UNION ALL
5 SELECT 3 wbs_id,'SI' wbs_code,'1' parent_wbs_id FROM DUAL UNION ALL
6 SELECT 4 wbs_id,'SI' wbs_code,'3' parent_wbs_id FROM DUAL
7 )
8 SELECT m.* FROM (
9 SELECT t.*,SUBSTR(SYS_CONNECT_BY_PATH(t.wbs_code,'.'),2) wbs_path FROM t
10 START WITH t.parent_wbs_id IS NULL
11 CONNECT BY PRIOR t.wbs_id = t.parent_wbs_id
12 ) m
13 WHERE m.wbs_path = 'NRG00870.SI'
14 ;

WBS_ID WBS_CODE PARENT_WBS_ID WBS_PATH

---

     1 SI       0             NRG00870.SI

[/code]