Oracle 21c递归SQL结果与SQL Server不一致问题求助

背景 #

• 同时使用SQL Server 2022与Oracle 21c，常将单一数据库的解决方案适配到另一数据库以学习提升
• 基于SQL Server的层级ID检查问题，适配到Oracle 21c后结果出现不一致

问题现象 #

• 未修改脚本时，按location_id升序排列，location_id=6及之前结果正确，之后全部错误
• 修改递归部分row_number的partition by tree1.location_order后，除location_id=10外结果正常
• 尝试按tree1.location_id降序排序修复location_id=10的问题时，location_id=6又出现错误
• 推测问题出在递归部分row_number的排序逻辑，但无法理解背后原理

辅助分析操作 #

• 添加RNPROB字段辅助分析
• 本地Oracle环境可正常运行测试脚本（DB Fiddle插入语句报错）

期望输出 #

location_id parent_id   level   location_order  location_name   location_name_hierarchy row_number_hierarchy    location_id_hierarchy
1   NULL    0   1   Location 1  Location 1  1   1
2   1   1   2   Location 2  Location 1/Location 2   1/2 1/2
3   1   1   1   Location 3  Location 1/Location 3   1/1 1/3
4   1   1   3   Location 4  Location 1/Location 4   1/3 1/4
5   2   2   1   Location 5  Location 1/Location 2/Location 5    1/2/1   1/2/5
6   5   3   1   Location 6  Location 1/Location 2/Location 5/Location 6 1/2/1/1 1/2/5/6
7   2   2   2   Location 7  Location 1/Location 2/Location 7    1/2/2   1/2/7
8   7   3   1   Location 8  Location 1/Location 2/Location 7/Location 8 1/2/2/1 1/2/7/8
9   3   2   1   Location 9  Location 1/Location 3/Location 9    1/1/1   1/3/9
10  9   3   1   Location 10 Location 1/Location 3/Location 9/Location 10    1/1/1/1 1/3/9/10
11  4   2   2   Location 11 Location 1/Location 4/Location 11   1/3/2   1/4/11
12  4   2   1   Location 12 Location 1/Location 4/Location 12   1/3/1   1/4/12

解决方案及原理 #

正确的Oracle递归查询脚本 #

WITH tree AS (
    SELECT 
        location_id,
        parent_id,
        0 AS level,
        location_order,
        location_name,
        location_name AS location_name_hierarchy,
        CAST(1 AS VARCHAR2(100)) AS row_number_hierarchy,
        CAST(location_id AS VARCHAR2(100)) AS location_id_hierarchy,
        CAST(location_order AS VARCHAR2(100)) AS order_path
    FROM locations
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        child.location_id,
        child.parent_id,
        parent.level + 1 AS level,
        child.location_order,
        child.location_name,
        parent.location_name_hierarchy || '/' || child.location_name AS location_name_hierarchy,
        parent.row_number_hierarchy || '/' || 
            ROW_NUMBER() OVER (PARTITION BY child.parent_id ORDER BY child.location_order) AS row_number_hierarchy,
        parent.location_id_hierarchy || '/' || child.location_id AS location_id_hierarchy,
        parent.order_path || '/' || child.location_order AS order_path
    FROM tree parent
    JOIN locations child ON parent.location_id = child.parent_id
)
SELECT 
    location_id,
    parent_id,
    level,
    location_order,
    location_name,
    location_name_hierarchy,
    row_number_hierarchy,
    location_id_hierarchy
FROM tree
ORDER BY order_path;

原理解析 #

1. 核心问题修正：递归中的分区与排序逻辑

   • 原适配错误在于row_number的分区字段错误，必须按child.parent_id分区，而非tree1.location_order或其他字段。因为我们需要对每个父节点下的子节点，按location_order重新编号，才能生成正确的层级序号路径。
   • SQL Server与Oracle的递归CTE执行顺序存在细微差异，错误的分区/排序逻辑会导致局部巧合正确、整体错误的现象——部分父节点的子节点恰好符合排序巧合，其他节点则不符合。

2. order_path字段的作用

   • 构建order_path字段（节点location_order的层级拼接），最终按该字段排序，确保所有节点严格按照层级内的location_order顺序排列，与期望结果完全匹配。

3. 之前尝试局部错误的原因

   • 按tree1.location_order分区时，会将不同父节点但父节点location_order相同的子节点归为一组，导致序号计算错误，仅部分父节点的子节点因分组巧合显示正常。
   • 按location_id降序排序时，会打乱子节点按location_order的排序逻辑，导致原本正确的节点（如location_id=6）的层级序号计算错误。

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