我在這里創建了我的小提琴示例：FIDDLE 這里也是來自小提琴的代碼：
CREATE TABLE T1(ID INT, CODE INT, CODE_NAME VARCHAR(100), PARENT_ID INT);
INSERT INTO T1 VALUES(100,1,'LEVEL 1', NULL);
INSERT INTO T1 VALUES(110,11,'LEVEL 2', 100);
INSERT INTO T1 VALUES(120,111,'LEVEL 3', 110);
INSERT INTO T1 VALUES(125,112,'LEVEL 3', 110);
INSERT INTO T1 VALUES(130,1111,'LEVEL 4', 120);
INSERT INTO T1 VALUES(200,2,'LEVEL 1', NULL);
INSERT INTO T1 VALUES(210,21,'LEVEL 2', 200);
INSERT INTO T1 VALUES(300,3,'LEVEL 1', NULL);
我很難找到如何從該表中獲得這個結果的靈魂調整：
| CODE | CODE NAME | CODE 1 |CODE NAME 1| CODE 2 | CODE NAME 2| CODE 3 | CODE NAME 3 |
-------- ------------ -------- ----------- -------- ------------ -------- -------------
| 1 | LEVEL 1 | 11 | LEVEL 2 | 111 | LEVEL 3 | 1111 | LEVEL 4 |
| 1 | LEVEL 1 | 11 | LEVEL 2 | 112 | LEVEL 3 | 112 | LEVEL 3 |
| 2 | LEVEL 1 | 21 | LEVEL 2 | 21 | LEVEL 2 | 21 | LEVEL 2 |
| 3 | LEVEL 1 | 3 | LEVEL 1 | 3 | LEVEL 1 | 3 | LEVEL 1 |
我已經嘗試過一些東西，connect by但這不是我需要的（我認為）......我將擁有的最大值是 4 個級別，如果資料中只有兩個級別，那么第 3 和第 4 級別應該填充最后一個現有值的值。如果有 3 個級別或 1 個級別，則相同的規則有效。
uj5u.com熱心網友回復：
對于您發布的示例資料：
SQL> select * from t1;
ID CODE CODE_NAME PARENT_ID
---------- ---------- ---------- ----------
100 1 LEVEL 1
110 11 LEVEL 2 100
120 111 LEVEL 3 110
130 1111 LEVEL 4 120
200 2 LEVEL 1
210 21 LEVEL 2 200
6 rows selected.
SQL>
但是，回傳所需結果的丑陋（并且誰知道如何執行）查詢是
with temp as
(select id, code, code_name, parent_id, level lvl,
row_number() over (partition by level order by id) rn
from t1
start with parent_id is null
connect by prior id = parent_id
),
a as
(select * from temp where lvl = 1),
b as
(select * from temp where lvl = 2),
c as
(select * from temp where lvl = 3),
d as
(select * from temp where lvl = 4)
select
a.code code1, a.code_name code_name1,
coalesce(b.code, a.code) code2, coalesce(b.code_name, a.code_name) code_name2,
coalesce(c.code, b.code, a.code) code3, coalesce(c.code_name, b.code_name, a.code_name) code_name3,
coalesce(d.code, c.code, b.code, a.code) code4, coalesce(d.code_name, c.code_name, b.code_name, a.code_name) code_name4
from a join b on b.rn = a.rn
left join c on c.rn = b.rn
left join d on d.rn = c.rn;
這導致
CODE1 CODE_NAME1 CODE2 CODE_NAME2 CODE3 CODE_NAME3 CODE4 CODE_NAME4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 LEVEL 1 11 LEVEL 2 111 LEVEL 3 1111 LEVEL 4
2 LEVEL 1 21 LEVEL 2 21 LEVEL 2 21 LEVEL 2
它有什么作用？
tempCTE 創建了一個層次結構；此外，row_number函式編號同一級別內的每一行a, b, c, dCTE提取屬于自己級別值的值（你說最多可以有4個級別）- 最后，
coalesce在列名上outer join做這項作業
uj5u.com熱心網友回復：
您可以使用遞回子查詢：
WITH hierarchy (
code, code_name,
code1, code_name1,
code2, code_name2,
code3, code_name3,
id, depth
) AS (
SELECT code,
code_name,
CAST(NULL AS INT),
CAST(NULL AS VARCHAR2(100)),
CAST(NULL AS INT),
CAST(NULL AS VARCHAR2(100)),
CAST(NULL AS INT),
CAST(NULL AS VARCHAR2(100)),
id,
1
FROM t1
WHERE parent_id IS NULL
UNION ALL
SELECT h.code,
h.code_name,
CASE depth WHEN 1 THEN COALESCE(t1.code, h.code) ELSE h.code1 END,
CASE depth WHEN 1 THEN COALESCE(t1.code_name, h.code_name) ELSE h.code_name1 END,
CASE depth WHEN 2 THEN COALESCE(t1.code, h.code1) ELSE h.code2 END,
CASE depth WHEN 2 THEN COALESCE(t1.code_name, h.code_name1) ELSE h.code_name2 END,
CASE depth WHEN 3 THEN COALESCE(t1.code, h.code2) ELSE h.code3 END,
CASE depth WHEN 3 THEN COALESCE(t1.code_name, h.code_name2) ELSE h.code_name3 END,
t1.id,
h.depth 1
FROM hierarchy h
LEFT OUTER JOIN t1
ON (h.id = t1.parent_id)
WHERE depth < 4
)
CYCLE code, depth SET is_cycle TO 1 DEFAULT 0
SELECT code, code_name,
code1, code_name1,
code2, code_name2,
code3, code_name3
FROM hierarchy
WHERE depth = 4;
其中，對于樣本資料：
CREATE TABLE T1(ID, CODE, CODE_NAME, PARENT_ID) AS
SELECT 100, 1, 'LEVEL 1', NULL FROM DUAL UNION ALL
SELECT 110, 11, 'LEVEL 2', 100 FROM DUAL UNION ALL
SELECT 120, 111, 'LEVEL 3', 110 FROM DUAL UNION ALL
SELECT 130, 1111, 'LEVEL 4', 120 FROM DUAL UNION ALL
SELECT 200, 2, 'LEVEL 1', NULL FROM DUAL UNION ALL
SELECT 210, 21, 'LEVEL 2a', 200 FROM DUAL UNION ALL
SELECT 220, 22, 'LEVEL 2b', 200 FROM DUAL UNION ALL
SELECT 230, 221, 'LEVEL 3', 220 FROM DUAL UNION ALL
SELECT 300, 3, 'LEVEL 1', NULL FROM DUAL;
輸出：
代碼 代碼名稱 代碼1 CODE_NAME1 代碼2 CODE_NAME2 代碼3 CODE_NAME3 1 1級 11 2級 111 3級 1111 4級 3 1級 3 1級 3 1級 3 1級 2 1級 21 2a級 21 2a級 21 2a級 2 1級 22 2b級 221 3級 221 3級
db<>在這里擺弄
uj5u.com熱心網友回復：
從您的示例中，我假設您希望每個根鍵看到一行，因為您的示例不是真正的樹而是竹子
如果是這樣，這是一個微不足道的PIVOT查詢 - 不幸的是僅限于某個級別的深度（這里是您的 4 個級別的示例）
with p (ROOT_CODE, CODE, CODE_NAME, ID, PARENT_ID, LVL) as (
select CODE, CODE, CODE_NAME, ID, PARENT_ID, 1 LVL from t1 where PARENT_ID is NULL
union all
select p.ROOT_CODE, c.CODE, c.CODE_NAME, c.ID, c.PARENT_ID, p.LVL 1 from t1 c
join p on c.PARENT_ID = p.ID),
t2 as (
select ROOT_CODE, CODE,CODE_NAME,LVL from p)
select * from t2
PIVOT
(max(CODE) code, max(CODE_NAME) code_name
for LVL in (1 as "LEV1",2 as "LEV2",3 as "LEV3",4 as "LEV4")
);
ROOT_CODE LEV1_CODE LEV1_CODE_ LEV2_CODE LEV2_CODE_ LEV3_CODE LEV3_CODE_ LEV4_CODE LEV4_CODE_
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 LEVEL 1 11 LEVEL 2 111 LEVEL 3 1111 LEVEL 4
2 2 LEVEL 1 21 LEVEL 2
該遞回CTE計算ROOT_CODE所需的支點。我離開作為練習，用之前的值填充未定義的級別（使用 COALESCE），如您的示例所示。
如果（如評論中所述）您為每個離開鍵添加一行，則基于一個簡單的解決方案CONNECT_BY_PATH是可能的。
我再次使用 *遞回 CTE calculating the path from *root* to the *current node* and finaly filtering in the result the *leaves* (ID that are notPARENT_ID`)
with p ( CODE, CODE_NAME, ID, PARENT_ID, PATH) as (
select CODE, CODE_NAME, ID, PARENT_ID, to_char(CODE)||'|'||CODE_NAME PATH from t1 where PARENT_ID is NULL
union all
select c.CODE, c.CODE_NAME, c.ID, c.PARENT_ID, p.PATH ||'|'||to_char(c.CODE)||'|'||c.CODE_NAME from t1 c
join p on c.PARENT_ID = p.ID)
select PATH from p
where ID in (select ID from T1 MINUS select PARENT_ID from T1)
order by 1;
結果適用于任何級別的深度，并且是帶有分隔符的連接字串
PATH
----------------------------------------------
1|LEVEL 1|11|LEVEL 2|111|LEVEL 3|1111|LEVEL 4
1|LEVEL 1|11|LEVEL 2|112|LEVEL 3
2|LEVEL 1|21|LEVEL 2
3|LEVEL 1
使用substr instr提取和coalesce默認值。
uj5u.com熱心網友回復：
使用分層查詢的解決方案 - 我們記錄 code 和 code_name 路徑，然后將它們分開。Level 用于決定我們是從路徑還是從葉節點填充資料。該解決方案假定代碼和代碼名稱不包含正斜杠字符（如果可以，請在路徑中使用另一個分隔符 - 可能是一些控制字符，如chr(31)ASCII 和 Unicode 中的單位分隔符）。
為了分解路徑，我使用regexp_substr了它，因為它更容易使用（而且，我假設所有代碼和代碼名稱都是非空的——如果它們可能是空的，那么解決方案可以很容易地適應）。如果證明這很慢，則可以更改為使用標準字串函式。
with
p (code, code_name, parent_id, lvl, code_pth, code_name_pth) as (
select code, code_name, parent_id, level,
sys_connect_by_path(code, '/') || ',' ,
sys_connect_by_path(code_name, '/') || ','
from t1
where connect_by_isleaf = 1
start with parent_id is null
connect by parent_id = prior id
)
select case when lvl = 1 then code
else to_number(regexp_substr(code_pth, '[^/] ', 1, 1)) end as code,
case when lvl =1 then code_name
else regexp_substr(code_name_pth, '[^/] ', 1, 1) end as code_name,
case when lvl <= 2 then code
else to_number(regexp_substr(code_pth, '[^/] ', 1, 2)) end as code_1,
case when lvl <= 2 then code_name
else regexp_substr(code_name_pth, '[^/] ', 1, 2) end as code_name_1,
case when lvl <= 3 then code
else to_number(regexp_substr(code_pth, '[^/] ', 1, 3)) end as code_2,
case when lvl <= 3 then code_name
else regexp_substr(code_name_pth, '[^/] ', 1, 3) end as code_name_2,
code as code_3,
code_name as code_name_3
from p;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/388842.html
標籤：甲骨文
上一篇：TO_CHAR在SQL查詢中失敗