假设当child_id值都为空（在另一个非空值之前）时，连续的m_id值与同一示例关联，则可以使用两个要匹配的联接执行此任务：

• 表1父m_id与表1子m_id
• 表1的子m_id与表2的m_id

SELECT t11.m_id, 
       t11.child_id,
       t2.make,
       t2.model,
       t11.op_status
FROM       tab1 t11 
INNER JOIN tab1 t12
        ON t11.m_id >= t12.child_id
INNER JOIN (SELECT DISTINCT m_id, make, model FROM tab2) t2
        ON t12.child_id = t2.m_id

输出：

| 中间标识|子项标识|操作状态|制造|模型|
| - ------|- ------|- ------|- ------|- ------|
| 1个|零|完成|宏碁|阿斯 perl |
| 第二章|零|完成|宏碁|阿斯 perl |
| 三个|零|完成|宏碁|阿斯 perl |
| 四个|1个|进行中的|宏碁|阿斯 perl |
检查here演示。
如果您有更复杂的间隙和孤岛问题，您可能需要通过以下方式重新生成分区（m_id唯一值）：

• 创建指示何时更改分区的标志
• 计算您的旗帜的运行总和
• 收集每个分区的make、model的相应值

WITH cte AS (
    SELECT t11.m_id, 
           t11.op_status,
           CASE WHEN t12.child_id IS NOT NULL 
                  OR LAG(t11.child_id) OVER(ORDER BY t11.m_id) IS NOT NULL
                THEN 1
           END AS change_part,
           t11.child_id,
           t2.make,
           t2.model
    FROM      tab1 t11
    LEFT JOIN tab1 t12
           ON t11.m_id = t12.child_id
    LEFT JOIN (SELECT DISTINCT m_id, make, model FROM tab2) t2
           ON t12.child_id = t2.m_id
), cte2 AS (
    SELECT m_id, 
           op_status, 
           child_id, 
           make, 
           model,
           SUM(change_part) OVER(ORDER BY m_id) AS parts
    FROM cte
)
SELECT m_id, 
       op_status, 
       child_id,
       MAX(make)     OVER(PARTITION BY parts) AS make,
       MAX(model)    OVER(PARTITION BY parts) AS model
FROM cte2
ORDER BY m_id

查看here演示。