家谱树分支提取首个符合条件节点的SQL实现及优化求助

假设你的家谱表结构如下（可根据实际表名、字段调整）：

CREATE TABLE family_tree (
    node_id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT, -- 父节点ID，Tony的parent_id为NULL或0
    hair_color VARCHAR(20),
    eye_color VARCHAR(20)
);

核心思路：递归CTE + 分支内排序取首 #

利用递归CTE遍历从Tony出发的所有家族分支，同时传递顶层祖先Tony的眼睛颜色，并在每个分支内筛选出首个满足「眼睛颜色与Tony不同」的节点。

完整SQL代码：

WITH recursive_family AS (
    -- 锚点：定位祖先Tony，记录顶层眼睛颜色
    SELECT 
        node_id,
        name,
        parent_id,
        eye_color AS current_eye,
        eye_color AS top_ancestor_eye, -- 传递Tony的眼睛颜色
        1 AS level,
        CAST(node_id AS VARCHAR(100)) AS branch_path -- 记录分支路径，用于分组
    FROM family_tree
    WHERE name = 'Tony'

    UNION ALL

    -- 递归：遍历子节点，继承顶层眼睛颜色
    SELECT 
        ft.node_id,
        ft.name,
        ft.parent_id,
        ft.eye_color AS current_eye,
        rf.top_ancestor_eye,
        rf.level + 1 AS level,
        CONCAT(rf.branch_path, ',', ft.node_id) AS branch_path
    FROM family_tree ft
    JOIN recursive_family rf ON ft.parent_id = rf.node_id
    -- 提前过滤：只继续遍历还没找到符合条件节点的分支
    WHERE NOT EXISTS (
        SELECT 1 FROM recursive_family rf_inner
        WHERE rf_inner.branch_path LIKE CONCAT(rf.branch_path, '%')
        AND rf_inner.current_eye != rf_inner.top_ancestor_eye
    )
),
-- 筛选所有符合条件的节点，并在每个分支内取第一个
branch_first_match AS (
    SELECT 
        node_id,
        name,
        top_ancestor_eye,
        current_eye,
        branch_path,
        ROW_NUMBER() OVER (PARTITION BY branch_path ORDER BY level) AS rn
    FROM recursive_family
    WHERE current_eye != top_ancestor_eye
)
SELECT node_id, name, top_ancestor_eye AS tony_eye_color, current_eye AS match_eye_color
FROM branch_first_match
WHERE rn = 1;

代码说明 #

1. 递归CTE部分：
   • 锚点成员精准定位Tony，初始化分支路径和顶层眼睛颜色。
   • 递归成员只遍历尚未找到符合条件节点的分支（通过NOT EXISTS判断），避免无效遍历。
2. 分支排序取首：
   • 用ROW_NUMBER()按分支路径分组，按层级排序，确保每个分支只取第一个满足条件的节点。

替代方案（Oracle专用CONNECT BY） #

如果使用Oracle数据库，可结合CONNECT_BY_ROOT获取顶层Tony的眼睛颜色，再用ROW_NUMBER()筛选：

WITH family_paths AS (
    SELECT 
        node_id,
        name,
        eye_color,
        CONNECT_BY_ROOT eye_color AS tony_eye_color,
        LEVEL AS node_level,
        SYS_CONNECT_BY_PATH(node_id, ',') AS branch_path
    FROM family_tree
    START WITH name = 'Tony'
    CONNECT BY PRIOR node_id = parent_id
),
ranked_matches AS (
    SELECT 
        node_id,
        name,
        tony_eye_color,
        eye_color,
        ROW_NUMBER() OVER (PARTITION BY branch_path ORDER BY node_level) AS rn
    FROM family_paths
    WHERE eye_color != tony_eye_color
)
SELECT node_id, name, tony_eye_color, eye_color
FROM ranked_matches
WHERE rn = 1;

关键注意点 #

• 确保parent_id关联正确，Tony的parent_id需设为NULL或无父节点标识。
• 若初始条件需要同时满足「发色为Black」，可在递归CTE的筛选条件中加入hair_color = 'Black'（根据需求调整位置：是仅Tony需要Black发色，还是分支节点需要？需明确逻辑后修改）。

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