因此，示例数据如下：

WITH lu_rd AS (
    SELECT * FROM VALUES
        (156386,  7731100, 105, 'Y'),
        (156387,  7731101, 105, 'Y'),
        (1360780, 1360780, 101, 'Y'),
        (1360781, 1360781, 101, 'Y')
        v(sub_id,  cust_order_id,   loc_id, active_flag)
), order_rel AS (
    SELECT * FROM VALUES 
        (7731100, 8081859),
        (7731101, 8081859),
        (1360780, 7110643),
        (1360781, 7110643)
        v(parentorder_id,  childorder_id)
), header_table AS (
    SELECT * FROM VALUES
        (2782, 7110643),
        (2584, 8081859)
        v(header_key, order_id)
), line_table AS (
    SELECT * FROM VALUES
        (7110643, 2799, 2782),
        (7110643, 2793, 2782),
        (8081859, 2585, 2584),
        (8081859, 2586, 2584)
        v(order_id, lin_key, header_key)
),

重新编写sql，同时使用cte删除一些括号。。

child_orders AS (
    SELECT
        s.sub_id,
        wo.childorder_id AS order_id
    FROM lu_rd AS s 
    JOIN order_rel wo 
        ON wo.parentorder_id = s.cust_order_id
    WHERE s.active_flag = 'Y'
), parent_orders AS (
    SELECT
        s.sub_id,
        wo.parentorder_id AS order_id
    FROM lu_rd AS s 
    JOIN order_rel AS wo 
        ON wo.childorder_id = s.cust_order_id
    WHERE s.active_flag = 'Y'
), the_union AS (
    SELECT * FROM child_orders
    UNION ALL
    SELECT * FROM parent_orders
)
SELECT h.header_key,
    l.lin_key,
    a.sub_id,
    a.order_id AS order_id 
FROM the_union AS a
JOIN header_table AS h
    ON a.order_id = h.order_id
JOIN line_table AS l 
    ON l.header_key = h.header_key

你确实得到了一些双重待遇：

HEADER_KEY   LIN_KEY    SUB_ID    ORDER_ID
2584         2585       156386    8081859
2584         2586       156386    8081859
2584         2585       156387    8081859
2584         2586       156387    8081859
2782         2799       1360780   7110643
2782         2793       1360780   7110643
2782         2799       1360781   7110643
2782         2793       1360781   7110643

所以把它剥回去一点：

<include data with>
child_orders AS (
    SELECT
        s.sub_id,
        wo.childorder_id AS order_id
    FROM lu_rd AS s 
    JOIN order_rel wo 
        ON wo.parentorder_id = s.cust_order_id
    WHERE s.active_flag = 'Y'
), parent_orders AS (
    SELECT
        s.sub_id,
        wo.parentorder_id AS order_id
    FROM lu_rd AS s 
    JOIN order_rel AS wo 
        ON wo.childorder_id = s.cust_order_id
    WHERE s.active_flag = 'Y'
)
    SELECT *, 'c' as t FROM child_orders
    UNION ALL
    SELECT *, 'p' as t FROM parent_orders
;

它为您提供了以下数据：

SUB_ID  ORDER_ID    T
156386  8081859 c
156387  8081859 c
1360780 7110643 c
1360781 7110643 c

这并不奇怪，因为这是你的数据。
因此，根据您的业务需要，您需要决定如何放弃sub\u id以仅获得单个订单\u id
一种方法是取第一个（最低的sub\u id）和放置在联合区域中的最简单的位置。

), the_union AS (
    SELECT * FROM (
      SELECT *, 'c' as t FROM child_orders
      UNION ALL
      SELECT *, 'p' as t FROM parent_orders
    )
    QUALIFY row_number() over (partition by order_id order by sub_id) = 1
)

它给出：

SUB_ID  ORDER_ID    T
1360780 7110643 c
156386  8081859 c

但是，当我们将其放回到您的完整sql中时，我们得到：

HEADER_KEY  LIN_KEY SUB_ID  ORDER_ID
2782        2799    1360780 7110643
2782        2793    1360780 7110643
2584        2585    156386  8081859
2584        2586    156386  8081859

因为你 line_table 两个都有副本 order_id 以及 header_key ，所以我假设这是有意的。