我有一個具有父/子結構的表，其父列指向子列上的子列，子列可能是父級或祖先...所以我希望該父級的基本 ID 可以是 3、4、5。 ..等等。級別甚至 1 級...這是應該的輸出
select
ID,
REF_ID,
ROUND_ID,
PARENT.ID BASE_ID --- HERE SHOULD BE THE ROOT ID OF THE PARENT
FROM
TAB CHILD LEFT JOIN
TAB PARENT
ON PARENT.ID = CHILD.REF_ID
uj5u.com熱心網友回復：
您可以使用分層查詢并查找層次結構的葉子：
SELECT CONNECT_BY_ROOT id AS id,
CONNECT_BY_ROOT ref_id AS ref_id,
CONNECT_BY_ROOT round_id AS round_id,
id AS base_id
FROM TAB
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ref_id = id;
其中，對于樣本資料：
CREATE TABLE tab (id, ref_id, round_id) AS
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, NULL, 1 FROM DUAL UNION ALL
SELECT 3, NULL, 1 FROM DUAL UNION ALL
SELECT 4, NULL, 1 FROM DUAL UNION ALL
SELECT 5, NULL, 2 FROM DUAL UNION ALL
SELECT 6, NULL, 2 FROM DUAL UNION ALL
SELECT 7, 1, 2 FROM DUAL UNION ALL
SELECT 8, NULL, 2 FROM DUAL UNION ALL
SELECT 9, 2, 2 FROM DUAL UNION ALL
SELECT 10, 3, 2 FROM DUAL UNION ALL
SELECT 11, 4, 2 FROM DUAL UNION ALL
SELECT 12, NULL, 2 FROM DUAL UNION ALL
SELECT 13, NULL, 2 FROM DUAL UNION ALL
SELECT 14, NULL, 2 FROM DUAL UNION ALL
SELECT 15, NULL, 3 FROM DUAL UNION ALL
SELECT 16, 8, 3 FROM DUAL UNION ALL
SELECT 17, NULL, 3 FROM DUAL UNION ALL
SELECT 18, 9, 3 FROM DUAL UNION ALL
SELECT 19, NULL, 3 FROM DUAL UNION ALL
SELECT 20, NULL, 3 FROM DUAL UNION ALL
SELECT 21, NULL, 3 FROM DUAL;
輸出：
ID REF_ID ROUND_ID BASE_ID 1 空值 1 1 2 空值 1 2 3 空值 1 3 4 空值 1 4 5 空值 2 5 6 空值 2 6 7 1 2 1 8 空值 2 8 9 2 2 2 10 3 2 3 11 4 2 4 12 空值 2 12 13 空值 2 13 14 空值 2 14 15 空值 3 15 16 8 3 8 17 空值 3 17 18 9 3 2 19 空值 3 19 20 空值 3 20 21 空值 3 21
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/442079.html
標籤：甲骨文