Oracle SQL如何为层次关系数据集新增自定义Depth深度字段

• 首先对原数据中Fiedl1_Id <> Fiedl2_Id的记录，按Fiedl2降序排序，为每个分组分配唯一的顺位序号
• 每个分组需要生成的Depth序列长度为6 - 顺位序号（第一个分组顺位为1，生成长度5；第二个顺位为2，生成长度4，以此类推，正好符合需求的递减规则）
• 单独处理Fiedl1_Id = Fiedl2_Id的记录，直接赋值Depth为0
• 最后合并两部分数据，按需求排序输出

兼容全版本Oracle写法 #

将代码中original_data的部分替换为你原本的查询逻辑即可：

WITH original_data AS (
    -- 此处替换为你原本的SQL查询语句
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2348 Fiedl2_Id, '949T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2349 Fiedl2_Id, '699T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2356 Fiedl2_Id, '649T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2379 Fiedl2_Id, '399T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2383 Fiedl2_Id, '299T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2470 Fiedl2_Id, '199T' Fiedl2 FROM DUAL
),
group_rn AS (
    -- 为非ID相等的分组分配降序顺位
    SELECT 
        t.*,
        ROW_NUMBER() OVER(ORDER BY Fiedl2 DESC) rn
    FROM original_data t
    WHERE Fiedl1_Id <> Fiedl2_Id
)
-- 生成各分组的Depth序列
SELECT 
    g.Fiedl1_Id,
    g.Fiedl1,
    g.Fiedl2_Id,
    g.Fiedl2,
    s.column_value Depth
FROM group_rn g
CROSS JOIN TABLE(
    CAST(
        MULTISET(
            SELECT LEVEL FROM DUAL 
            CONNECT BY LEVEL <= 6 - g.rn
        ) AS SYS.ODCINUMBERLIST
    )
) s
UNION ALL
-- 合并Depth为0的特殊记录
SELECT 
    Fiedl1_Id,
    Fiedl1,
    Fiedl2_Id,
    Fiedl2,
    0 Depth
FROM original_data
WHERE Fiedl1_Id = Fiedl2_Id
ORDER BY Fiedl2 DESC, Depth;

Oracle 12c及以上简化写法 #

使用LATERAL关联简化集合类型转换逻辑，写法更直观：

WITH original_data AS (
    -- 此处替换为你原本的SQL查询语句
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2348 Fiedl2_Id, '949T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2349 Fiedl2_Id, '699T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2356 Fiedl2_Id, '649T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2379 Fiedl2_Id, '399T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2383 Fiedl2_Id, '299T' Fiedl2 FROM DUAL UNION ALL
    SELECT 2470 Fiedl1_Id, '199T' Fiedl1, 2470 Fiedl2_Id, '199T' Fiedl2 FROM DUAL
),
group_rn AS (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(ORDER BY Fiedl2 DESC) rn
    FROM original_data t
    WHERE Fiedl1_Id <> Fiedl2_Id
)
SELECT g.Fiedl1_Id, g.Fiedl1, g.Fiedl2_Id, g.Fiedl2, s.lv Depth
FROM group_rn g,
LATERAL (
    SELECT LEVEL lv FROM DUAL 
    CONNECT BY LEVEL <= 6 - g.rn
) s
UNION ALL
SELECT Fiedl1_Id, Fiedl1, Fiedl2_Id, Fiedl2, 0 Depth
FROM original_data
WHERE Fiedl1_Id = Fiedl2_Id
ORDER BY Fiedl2 DESC, Depth;

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