我相信像下面这样的相关查询是你想要的，根据你的数据，你想要最大的Name_Key，它属于一个在当前位置或之前的成年人：

select *, Coalesce((
  select Max(Name_Key)
    from t t2
    where t2.Name = 'Adult' 
      and t2.Position <= t.Position
  ), Name_Key) Parent_Name_Key
from t
order by Position;

字符串

你可以在没有子查询的情况下用analytic来做：

with data (Position, Name, Name_key ) as (
    select 1, 'Adult', 123 from dual union all
    select 2, 'Child', 555 from dual union all
    select 3, 'Child', 666 from dual union all
    select 4, 'Adult', 456 from dual union all
    select 5, 'Child', 777 from dual union all 
    select 6, 'Adult', 789 from dual union all
    select 7, 'Child', 888 from dual union all
    select 8, 'Child', 999 from dual -- union all
)
select d.*, last_value(decode(name, 'Adult', Name_key)) ignore nulls over(order by position) as parent_name_key
from data d
;

1   Adult   123 123
2   Child   555 123
3   Child   666 123
4   Adult   456 456
5   Child   777 456
6   Adult   789 789
7   Child   888 789
8   Child   999 789

字符串

这里是解决方案。我使用求和窗口函数来实现这一点。我认为可以有一些其他的方法来解决这个问题。

with assign_0_1 as
    (
        select 
            * , 
            case when Name = 'Adult' then 1 else 0 end as assign_0_1 , 
            sum(case when Name = 'Adult' then 1 else 0 end) over(order by position) as rolling_assign_0_1
        from relation
    )
    select b.position , b.name , b.Name_Key , a.Name_Key as parent_Name_key  from
    (select Name_key ,  rolling_assign_0_1  from assign_0_1 where assign_0_1 = 1) as a inner join assign_0_1 as b
    on a.rolling_assign_0_1 = b.rolling_assign_0_1;

字符串

在Oracle 12中，可以使用MATCH_RECOGNIZE：

SELECT position, name, name_key, parent_name_key
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY position
  MEASURES
    adult.name_key AS parent_name_key
  ALL ROWS PER MATCH
  PATTERN (adult child*)
  DEFINE adult AS name = 'Adult',
         child AS name = 'Child'
)

字符串
其中，对于样本数据：

CREATE TABLE table_name (Position, Name, Name_key ) AS
  SELECT 1, 'Adult', 123 FROM DUAL UNION ALL
  SELECT 2, 'Child', 555 FROM DUAL UNION ALL
  SELECT 3, 'Child', 666 FROM DUAL UNION ALL
  SELECT 4, 'Adult', 456 FROM DUAL UNION ALL
  SELECT 5, 'Child', 777 FROM DUAL UNION ALL 
  SELECT 6, 'Adult', 789 FROM DUAL UNION ALL
  SELECT 7, 'Child', 888 FROM DUAL UNION ALL
  SELECT 8, 'Child', 999 FROM DUAL;

型
产出：
| 位置|名称|名称_键|页面名称键|
| --|--|--|--|
| 1 |成人| 123 | 123 |
| 2 |孩子| 555 | 123 |
| 3 |孩子| 666 | 123 |
| 4 |成人| 456 | 456 |
| 5 |孩子| 777 | 456 |
| 6 |成人| 789 | 789 |
| 7 |孩子| 888 | 789 |
| 8 |孩子| 999 | 789 |
fiddle

您可以使用MODEL子句（它快速可靠）来创建新列，并在Position之后使用Parent name键：

WITH    --  S a m p l e   D a t a : 
    tbl (POSITION, A_NAME, NAME_KEY ) AS 
        (   Select 1, 'Adult', 123 From Dual Union All
            Select 2, 'Child', 555 From Dual Union All
            Select 3, 'Child', 666 From Dual Union All
            Select 4, 'Adult', 456 From Dual Union All
            Select 5, 'Child', 777 From Dual Union All 
            Select 6, 'Adult', 789 From Dual Union All
            Select 7, 'Child', 888 From Dual Union All
            Select 8, 'Child', 999 From dual 
        )
--    M a i n   S Q L :
Select  POSITION, A_NAME, NAME_KEY, PARENT_NAME_KEY
From    tbl
MODEL   Dimension By (POSITION)
        Measures (A_NAME, NAME_KEY, 0 "PARENT_NAME_KEY", 0 "PARENT_POSITION")
        RULES ( PARENT_POSITION[ANY] = Nvl(Case When A_NAME[CV()] = 'Adult' Then CV(POSITION) End, Max(PARENT_POSITION)[POSITION < CV(POSITION)]), 
                PARENT_NAME_KEY[ANY] =  NAME_KEY[POSITION = PARENT_POSITION[CV()]] 
              )
/*    R e s u l t :
  POSITION A_NAME   NAME_KEY PARENT_NAME_KEY
---------- ------ ---------- ---------------
         1 Adult         123             123
         2 Child         555             123
         3 Child         666             123
         4 Adult         456             456
         5 Child         777             456
         6 Adult         789             789
         7 Child         888             789
         8 Child         999             789   */

字符串
维度（此处用作唯一行寻址）已存在于列POSITION中。
RULES子句定义以下项的值：

• 对于任何维度（PANEL_POSITION[ANY]），如果是成人，则采用POSITION并保持该POSITION，直到另一个成人
• PANEL_NAME_KEY列读取最后一个成人的NAME_KEY（实际行的PANEL_POSITION）