Oracle PL / SQL непосредственно перед запросом корневого узла

Доброе утро,

Итак, у меня такая ситуация : Таблица данных, содержащая 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)

Мне нужно для каждого случая получить ближайший узел к корню. Это означает, что:

  • для W3, W4 или W5, я хочу получить информацию W1 (первый раз, красно-синий)
  • для W1 я ничего не хочу (или это собственная информация, может работать с этим)
  • для W0, я хочу ничего.
Надеясь, что я смогу достаточно ясно выразиться. Хорошего дня.
3 2
hierarchical-data plsql

3 ответа:

С полным кредитом Pablomatico для начальной точки, посмотрите на это расширение его работы, которое включает обработку идентификаторов, которые не являются 2-значными, и повторно присоединяется к источнику, чтобы вернуть атрибуты строки just-before-root: 

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"
2

Как уже упоминалось, вы можете сделать это с помощью иерархического запроса. Вы можете построить путь к каждому узлу и извлечь узел непосредственно перед корнем из этой строки. Что-то вроде этого может помочь вам получить окончательное решение: 

WITH the_table AS (SELECT 'W0' item_id, NULL parent_id FROM DUAL
                   UNION
                   SELECT 'W1' item_id, 'W0' parent_id FROM DUAL
                   UNION
                   SELECT 'W2' item_id, 'W1' parent_id FROM DUAL
                   UNION
                   SELECT 'W3' item_id, 'W1' parent_id_idrent FROM DUAL
                   UNION
                   SELECT 'W4' item_id, 'W3' parent_id FROM DUAL
                   UNION
                   SELECT 'W5' item_id, 'W4' parent_id FROM DUAL
                   UNION
                   SELECT 'W6' item_id, 'W0' parent_id FROM DUAL
                   UNION
                   SELECT 'W7' item_id, 'W6' parent_id FROM DUAL
                   UNION
                   SELECT 'W8' item_id, 'W0' parent_id FROM DUAL)
SELECT item_id, 
       SYS_CONNECT_BY_PATH(item_id, '/') node_path, 
       SUBSTR(SYS_CONNECT_BY_PATH(item_id, '/'), 5, 2) just_before_root
FROM the_table
CONNECT BY PRIOR item_id = parent_id
START WITH parent_id IS NULL;
2
create or replace function getNodeCloserToRoot
(
  p_given_node varchar2(50);
)
return varchar2
is
 v_parent_node varchar2(50);
 v_current_node varchar2(50) := p_given_node;
begin
  select parentDataId 
     into v_parent_node
  from MyTable
  where dataId = v_current_node;
  loop
    exit when v_parent_node is null;
      v_current_node := v_parent_node;
      select parentDataId 
       into v_parent_node
      from MyTable
      where dataId = v_current_node;
  end loop;

  return v_current_node;
end;
0