如何基于两个字段的关联关系排序Oracle数据表？

针对你遇到的date字段重复导致old_value与new_value链式关联断裂的问题，可以通过**递归CTE（公共表表达式）**实现按id分组，同时保证每行old_value等于上一行new_value，且尽可能保留date的顺序。

核心思路 #

利用递归逻辑构建每个id分组内的关联链条：

1. 先找到每个分组的"起始行"（即没有其他行的new_value等于该行old_value的记录，也就是链条的起点）；
2. 递归地将后续关联行（old_value等于当前行new_value的记录）依次衔接，用自定义排序字段维护链条顺序；
3. 当存在多个候选关联行时，优先选择date更早的记录，兼顾关联逻辑与date顺序。

实现代码 #

基础版（保证关联优先） #

WITH recursive_sorted AS (
    -- 锚点成员：定位每个id分组的起始记录
    SELECT 
        id, date, old_value, new_value,
        1 AS sort_order
    FROM your_table t
    WHERE NOT EXISTS (
        SELECT 1 
        FROM your_table t2 
        WHERE t2.id = t.id 
          AND t2.new_value = t.old_value
    )
    UNION ALL
    -- 递归成员：依次衔接关联记录
    SELECT 
        t.id, t.date, t.old_value, t.new_value,
        rs.sort_order + 1 AS sort_order
    FROM your_table t
    JOIN recursive_sorted rs 
        ON t.id = rs.id 
        AND t.old_value = rs.new_value
    WHERE NOT EXISTS (
        SELECT 1 
        FROM recursive_sorted rs2 
        WHERE rs2.id = t.id 
          AND rs2.sort_order > rs.sort_order
          AND rs2.old_value = rs.new_value
    )
)
SELECT id, date, old_value, new_value
FROM recursive_sorted
ORDER BY id, sort_order;

优化版（兼顾date顺序） #

如果同一id分组内，存在多个old_value匹配当前行new_value的记录，优先选择date更早的行，避免date顺序过度混乱：

WITH recursive_sorted AS (
    SELECT 
        id, date, old_value, new_value,
        1 AS sort_order
    FROM your_table t
    WHERE NOT EXISTS (
        SELECT 1 
        FROM your_table t2 
        WHERE t2.id = t.id 
          AND t2.new_value = t.old_value
    )
    UNION ALL
    SELECT 
        t.id, t.date, t.old_value, t.new_value,
        rs.sort_order + 1 AS sort_order
    FROM your_table t
    JOIN recursive_sorted rs 
        ON t.id = rs.id 
        AND t.old_value = rs.new_value
    -- 优先选择date最早的候选行
    WHERE NOT EXISTS (
        SELECT 1 
        FROM your_table t3
        WHERE t3.id = rs.id 
          AND t3.old_value = rs.new_value
          AND t3.date < t.date
    )
    AND NOT EXISTS (
        SELECT 1 
        FROM recursive_sorted rs2 
        WHERE rs2.id = t.id 
          AND rs2.sort_order > rs.sort_order
          AND rs2.old_value = rs.new_value
    )
)
SELECT id, date, old_value, new_value
FROM recursive_sorted
ORDER BY id, sort_order;

注意事项 #

• 若数据存在循环关联（如A→B→A），递归会触发无限循环，Oracle默认限制递归深度为1000，也可手动添加CONNECT BY MAXDEPTH或在递归条件中加入深度限制；
• 若同一id分组内存在多个独立的关联链条，该方法会将所有链条按起始行的date顺序列出；
• 测试时建议先针对单个id分组验证结果，确认逻辑符合预期后再批量执行。

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