我試圖在 Oracle 11g 中創建一個父子層次結構樹，但在這種情況下
- 每個CODE 都是唯一的（沒有重復） ，并且在PARENT_ID中具有主要父級
- 但是當PARENT_ID IS LIKE '_999' 時有一個例外，那么他們有多個父母，其值在 ETC1 和 ETC2 之間
- 最大等級限制為 4
這是我打算做的樹： Flow Tree
以下是用于測驗的原始查詢
with t as (
select 'A1' CODE, 'JOB_1_1' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A2' CODE, 'JOB_1_2' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A3' CODE, 'JOB_1_3' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B01' CODE, 'JOB_2_1' CODE_NM, 'A1' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B02' CODE, 'JOB_2_2' CODE_NM, 'A2' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B03' CODE, 'JOB_2_3' CODE_NM, 'A3' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C001' CODE, 'JOB_3_1' CODE_NM, 'B01' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C002' CODE, 'JOB_3_2' CODE_NM, 'B02' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C003' CODE, 'JOB_3_3' CODE_NM, 'B03' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'D0001' CODE, 'JOB_4_1' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0002' CODE, 'JOB_4_2' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0003' CODE, 'JOB_4_3' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual
)
select * from t
我已經嘗試過的
SELECT LPAD(' ', 4*(LEVEL-1)) || CODE AS CODE, CODE_NM, PARENT_ID, LEVEL
FROM t
START WITH PARENT_ID = 'A'
CONNECT BY PRIOR CODE = PARENT_ID
;
結果：
-- The result is as you expected, Data with 'C999' PARENT_CODE doesn't show Because not connected anywhere
| CODE |CODE_NM | PARENT_ID|LEVEL|
| -----------| -------|----------|-----|
|A1 |JOB_1_1 | A |1 |
| B01 |JOB_2_1 | A1 |2 |
| C001|JOB_3_1 | B01 |3 |
|A2 |JOB_1_2 | A |1 |
| B02 |JOB_2_2 | A2 |2 |
| C002|JOB_3_2 | B02 |3 |
|A3 |JOB_1_3 | A |1 |
| B03 |JOB_2_3 | A3 |2 |
| C003|JOB_3_3 | B03 |3 |
我想要的是這樣的：
| CODE |CODE_NM | PARENT_ID|LEVEL|
| ----------- | -------|----------|-----|
|A1 |JOB_1_1 | A |1 |
| B01 |JOB_2_1 | A1 |2 |
| C001 |JOB_3_1 | B01 |3 |
| D0001|JOB_4_1 | |4 |
| D0002|JOB_4_2 | |4 |
| D0003|JOB_4_3 | |4 |
|A2 |JOB_1_2 | A |1 |
| B02 |JOB_2_2 | A2 |2 |
| C002 |JOB_3_2 | B02 |3 |
| D0001|JOB_4_1 | |4 |
| D0002|JOB_4_2 | |4 |
| D0003|JOB_4_3 | |4 |
|A3 |JOB_1_3 | A |1 |
| B03 |JOB_2_3 | A3 |2 |
| C003 |JOB_3_3 | B03 |3 |
| D0001|JOB_4_1 | |4 |
| D0002|JOB_4_2 | |4 |
| D0003|JOB_4_3 | |4 |
我檢查的所有來源要么在表中有代碼重復，要么只是制作一個新代碼來進行不同的分支，關于這個問題的任何輸入都將非常有幫助，并提前致謝。因為這個，我的頭已經很痛了
uj5u.com熱心網友回復：
在子句中添加OR條件以檢查 if ：CONNECT BYPRIOR code BETWEEN etc1 and etc2
with t as (
select 'A1' CODE, 'JOB_1_1' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A2' CODE, 'JOB_1_2' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A3' CODE, 'JOB_1_3' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B01' CODE, 'JOB_2_1' CODE_NM, 'A1' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B02' CODE, 'JOB_2_2' CODE_NM, 'A2' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B03' CODE, 'JOB_2_3' CODE_NM, 'A3' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C001' CODE, 'JOB_3_1' CODE_NM, 'B01' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C002' CODE, 'JOB_3_2' CODE_NM, 'B02' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C003' CODE, 'JOB_3_3' CODE_NM, 'B03' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'D0001' CODE, 'JOB_4_1' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0002' CODE, 'JOB_4_2' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0003' CODE, 'JOB_4_3' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual
)
SELECT LPAD(' ', 4*(LEVEL-1)) || CODE AS CODE, CODE_NM, PARENT_ID, LEVEL
FROM t
START WITH PARENT_ID = 'A'
CONNECT BY
PRIOR CODE = PARENT_ID
OR PRIOR CODE BETWEEN ETC1 AND ETC2
;
輸出：
代碼 CODE_NM PARENT_ID 等級 A1作業_1_1 一種 1 B01作業_2_1 A1 2 C001作業_3_1 B01 3 D0001作業_4_1 C999 4 D0002作業_4_2 C999 4 D0003作業_4_3 C999 4 A2作業_1_2 一種 1 B02作業_2_2 A2 2 C002作業_3_2 B02 3 D0001作業_4_1 C999 4 D0002作業_4_2 C999 4 D0003作業_4_3 C999 4 A3作業_1_3 一種 1 B03作業_2_3 A3 2 C003作業_3_3 B03 3 D0001作業_4_1 C999 4 D0002作業_4_2 C999 4 D0003作業_4_3 C999 4
如果您希望PARENT_ID列與層次結構的先前級別匹配，則類似于：
SELECT LPAD(' ', 4*(LEVEL-1)) || CODE AS CODE,
CODE_NM,
COALESCE(PRIOR code, parent_id) AS PARENT_ID,
LEVEL
FROM t
START WITH PARENT_ID = 'A'
CONNECT BY
PRIOR CODE = PARENT_ID
OR PRIOR CODE BETWEEN ETC1 AND ETC2
;
db<>fiddle here
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457337.html