SQL使用CASE WHEN结合row_number在同id0分组中配对提取关联ORDER_NUMBER

核心逻辑：需求本质为同id0分组内，按RN升序排序后相邻的两条数据配对，前一条作为主订单、后一条作为次订单，以下是两种可直接使用的SQL实现：

方案1：自连接实现（逻辑直观，适合小数据量场景） #

SELECT
    t1.Type AS Type_main,
    t2.Type AS Type_minor,
    t1.ORDER_NUMBER AS ORDER_NUMBER_main,
    t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_relation t1
INNER JOIN order_relation t2
    ON t1.id0 = t2.id0 
    AND t1.RN = t2.RN - 1
ORDER BY t1.id0, t1.RN;

注：请将代码中的order_relation替换为你的实际表名。

方案2：LEAD窗口函数实现（性能更优，适合大数据量场景） #

WITH order_with_next AS (
    SELECT
        id0,
        RN,
        Type,
        ORDER_NUMBER,
        -- 取同id0分组内按RN排序的下一条数据的类型和订单号
        LEAD(Type) OVER(PARTITION BY id0 ORDER BY RN) AS next_Type,
        LEAD(ORDER_NUMBER) OVER(PARTITION BY id0 ORDER BY RN) AS next_ORDER_NUMBER
    FROM order_relation
)
SELECT
    Type AS Type_main,
    next_Type AS Type_minor,
    ORDER_NUMBER AS ORDER_NUMBER_main,
    next_ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_with_next
WHERE next_Type IS NOT NULL -- 过滤每个分组最后一条无下级配对的记录
ORDER BY id0, RN;

注：两种方案输出结果与你给出的期望格式完全一致，可直接替换表名后运行测试。

内容的提问来源于stack exchange，提问作者Karina