Oracle层级查询中递归验证子记录的实现方案

源表结构与数据 #

现有source_t表结构及数据如下：

需求说明 #

• 为每个Item_no筛选出send_type = 'SUP'且flag = 0的记录
• 通过递归匹配(rec_code, rec_type)与(send_code, send_type)的关联关系，追溯该Item_no下所有关联子记录
• 若该Item_no的关联子记录中存在任意一条flag = 1，则将对应SUP记录的flag设为1；否则保持原flag值0
• 数据规模：1亿条记录，5万+不同Item_no，每个Item_no包含多条子记录

期望输出结果 #

处理后的目标结果如下：

解决方案（适用于支持递归CTE的数据库：PostgreSQL、SQL Server、MySQL 8+） #

针对大数据量场景，采用按Item_no分组递归的方式，避免全表递归带来的性能问题，具体SQL实现如下：

WITH recursive cte AS (
    -- 初始节点：每个Item_no的SUP记录
    SELECT 
        Item_no,
        rec_code,
        rec_type,
        flag AS has_flag_1
    FROM source_t
    WHERE send_type = 'SUP' AND flag = 0
    
    UNION ALL
    
    -- 递归关联子节点：匹配send_code=父节点rec_code，send_type=父节点rec_type
    SELECT 
        s.Item_no,
        s.rec_code,
        s.rec_type,
        CASE WHEN c.has_flag_1 = 1 OR s.flag = 1 THEN 1 ELSE 0 END AS has_flag_1
    FROM source_t s
    JOIN cte c 
        ON s.Item_no = c.Item_no 
        AND s.send_code = c.rec_code 
        AND s.send_type = c.rec_type
    WHERE c.has_flag_1 = 0 -- 已找到flag=1的节点无需继续递归
)
-- 聚合每个Item_no的SUP记录，确定最终flag值
SELECT 
    st.Item_no,
    st.rec_code,
    st.rec_type,
    st.send_code,
    st.send_type,
    COALESCE(MAX(c.has_flag_1), 0) AS flag
FROM source_t st
LEFT JOIN cte c ON st.Item_no = c.Item_no 
    AND st.rec_code = c.rec_code 
    AND st.rec_type = c.rec_type
WHERE st.send_type = 'SUP' AND st.flag = 0
GROUP BY st.Item_no, st.rec_code, st.rec_type, st.send_code, st.send_type;

性能优化建议 #

• 为source_t表创建复合索引：(Item_no, send_code, send_type)，加速递归关联的匹配速度
• 递归过程中加入c.has_flag_1 = 0的过滤条件，提前终止已找到目标节点的递归分支，减少计算量
• 若数据库支持，可开启并行查询优化大表的递归处理效率

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