Oracle GL树ARA40查询结果格式异常，求SQL优化方案

需要针对Oracle GL树层级查询实现以下效果：

• 每个叶子节点对应单独一行记录
• 根节点ARA40仅显示代码，不附带自身描述
• 子节点显示「代码|描述」的格式

原SQL语句 #

SELECT
  'FXE_I_823' AS KEY,
  listagg(ftn.pk1_start_value || '|' || ffvv.description, '|')
    within GROUP (ORDER BY DEPTH) "TREE_CODE"
FROM 
fnd_tree_node ftn,
fnd_flex_values_vl ffvv
WHERE 1=1
AND ftn.pk1_start_value = ffvv.flex_value
AND ftn.tree_code = 'ARA40'
AND ffvv.value_category = 'COST CENTER'

当前输出 #

ARA40|ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623501|1623501 - LOMG Retail Location|1623507|1623507 - Retail Freight Service ACIM

预期输出 #

ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623501|1623501 - LOMG Retail Location
ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623507|1623507 - Retail Freight Service ACIM

优化后的SQL #

WITH tree_hierarchy AS (
    SELECT 
        ftn.tree_code,
        ftn.pk1_start_value AS node_code,
        ffvv.description AS node_desc,
        ftn.depth,
        ftn.parent_pk1_value,
        -- 标记叶子节点（无下级子节点的节点）
        CASE WHEN NOT EXISTS (
            SELECT 1 FROM fnd_tree_node child_ftn 
            WHERE child_ftn.parent_pk1_value = ftn.pk1_start_value 
              AND child_ftn.tree_code = ftn.tree_code
        ) THEN 'Y' ELSE 'N' END AS is_leaf
    FROM fnd_tree_node ftn
    JOIN fnd_flex_values_vl ffvv 
        ON ftn.pk1_start_value = ffvv.flex_value
    WHERE ftn.tree_code = 'ARA40'
      AND ffvv.value_category = 'COST CENTER'
),
leaf_paths AS (
    SELECT 
        CONNECT_BY_ROOT node_code AS root_code,
        -- 根节点仅输出代码，其他节点输出代码+描述
        CASE WHEN depth = 1 THEN node_code ELSE node_code || '|' || node_desc END AS node_entry,
        depth,
        node_code AS leaf_code
    FROM tree_hierarchy
    WHERE is_leaf = 'Y'
    -- 从叶子节点向上遍历至根节点，构建完整路径
    CONNECT BY PRIOR parent_pk1_value = node_code
    START WITH is_leaf = 'Y'
)
SELECT
    'FXE_I_823' AS KEY,
    LISTAGG(node_entry, '|') WITHIN GROUP (ORDER BY depth) AS "TREE_CODE"
FROM leaf_paths
GROUP BY root_code, leaf_code;

关键优化说明 #

• tree_hierarchy CTE：预处理树节点数据，标记每个节点是否为叶子节点，同时关联获取节点描述
• leaf_paths CTE：使用Oracle层级查询CONNECT BY从每个叶子节点向上追溯完整路径，同时处理根节点的特殊显示逻辑
• 最终聚合：按根节点和叶子节点分组，用LISTAGG拼接路径，确保每个叶子节点对应独立的一行结果

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