早上好,
所以我有这种情况:包含dataID,parentdataID和一些信息的数据表
MyTable
-Name
-WorkingPeriod
-ColorScheme
-ID
-parentID
为了说明这一点,该表的工作方式如下:
W0 (Always, Red)
--W1 (First time, red-blue)
----W2 (First step, red-blue-grey)
----W3 (Second step, red-blue-black)
------W4 (...)
--------W5 (...)
--W6 (Second time, red-green)
----W7 (First step, red-green-grey)
--W8 (Third time, red-pink)
对于每种情况,我都需要获取最接近根的节点。意思是:
希望我能使自己足够清楚。祝你有美好的一天。
首先要完全归功于Pablomatico,请看一下他工作的扩展,包括处理长度不超过2位数字的ID,然后重新加入源代码,以带回刚好在根前行的属性:
WITH the_table AS (SELECT 'W0' item_id, NULL parent_id, 'Red' colour FROM DUAL
UNION
SELECT 'W1' item_id, 'W0' parent_id , 'blue' colour FROM DUAL
UNION
SELECT 'W2' item_id, 'W1' parent_id, 'Grey' colour FROM DUAL
UNION
SELECT 'W3' item_id, 'W1' parent_id, 'Black' colour FROM DUAL
UNION
SELECT 'W4' item_id, 'W3' parent_id, 'Mauve' colour FROM DUAL
UNION
SELECT 'W5' item_id, 'W4' parent_id, 'Orange' colour FROM DUAL
UNION
SELECT 'W6' item_id, 'W0' parent_id, 'Green' colour FROM DUAL
UNION
SELECT 'W7' item_id, 'W6' parent_id, 'Grey' colour FROM DUAL
UNION
SELECT 'W8' item_id, 'W0' parent_id, 'Pink' colour FROM DUAL)
SELECT main_tab.item_id, main_tab.colour,main_tab.just_before_root, the_Table.colour
FROM
(SELECT item_id,
colour,
SYS_CONNECT_BY_PATH(item_id, '/') node_path,
CASE WHEN INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,2) = 0 THEN NULL
WHEN INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,2) != 0
AND INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,3) = 0
THEN SUBSTR(SYS_CONNECT_BY_PATH(item_id, '/'), INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,2)+1)
ELSE SUBSTR(SYS_CONNECT_BY_PATH(item_id, '/'), INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,2)+1,INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,3) - INSTR(SYS_CONNECT_BY_PATH(item_id, '/'),'/',1,2) - 1 ) END just_before_root
FROM the_table
CONNECT BY PRIOR item_id = parent_id
START WITH parent_id IS NULL) main_tab
left outer join the_table on main_tab.just_before_root = the_table.item_id;
返回值:
ITEM_ID COLOUR JUST_BEFORE_ROOT COLOUR_1
"W0" "Red"
"W1" "blue" "W1" "blue"
"W2" "Grey" "W1" "blue"
"W3" "Black" "W1" "blue"
"W4" "Mauve" "W1" "blue"
"W5" "Orange" "W1" "blue"
"W6" "Green" "W6" "Green"
"W7" "Grey" "W6" "Green"
"W8" "Pink" "W8" "Pink"
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句