Oracle SQL层级查询与家庭分组排名需求求助

示例表结构与数据 #

首先定义示例父子关系表并插入测试数据（对应家庭数为3的场景）：

CREATE TABLE parent_child (
    parent_id VARCHAR2(10),
    child_id VARCHAR2(10)
);

INSERT INTO parent_child VALUES ('P1', 'C1');
INSERT INTO parent_child VALUES ('P2', 'C1');
INSERT INTO parent_child VALUES ('P3', 'C2');
INSERT INTO parent_child VALUES ('P4', 'C3');
INSERT INTO parent_child VALUES ('P5', 'C3');

核心查询语句 #

以下SQL通过层级查询识别关联家庭，生成家庭索引，并实现按家庭排序、同家庭内父节点优先的输出：

WITH family_groups AS (
    -- 遍历所有关联节点，确定每个节点的家庭根标识
    SELECT DISTINCT
        node_id,
        CONNECT_BY_ROOT root_node AS family_root
    FROM (
        -- 转换为无向边，确保所有关联节点都能被遍历到
        SELECT parent_id AS from_node, child_id AS to_node, parent_id AS root_node FROM parent_child
        UNION ALL
        SELECT child_id AS from_node, parent_id AS to_node, child_id AS root_node FROM parent_child
    ) edges
    START WITH from_node IS NOT NULL
    CONNECT BY NOCYCLE PRIOR to_node = from_node
    -- 为每个节点统一家庭根标识（取所有可达根中的最小值）
    MODEL
        PARTITION BY (node_id)
        DIMENSION BY (0 AS pos)
        MEASURES (family_root AS family_root)
        RULES (
            family_root[0] = MIN(family_root)[ANY]
        )
),
ranked_families AS (
    -- 生成连续的家庭索引，标记父节点优先级
    SELECT
        node_id,
        DENSE_RANK() OVER (ORDER BY family_root) AS index_family,
        CASE WHEN EXISTS (SELECT 1 FROM parent_child WHERE parent_id = node_id) THEN 1 ELSE 2 END AS parent_priority
    FROM family_groups
)
-- 最终排序输出
SELECT
    index_family,
    node_id,
    CASE parent_priority WHEN 1 THEN '父节点' ELSE '子节点' END AS node_type
FROM ranked_families
ORDER BY index_family, parent_priority, node_id;

关键逻辑说明 #

• 无向图转换：将父子关系双向展开，解决多对多场景下单向遍历遗漏关联节点的问题。
• 家庭根标识：通过CONNECT_BY_ROOT获取遍历起始节点，再用MODEL子句统一每个节点的家庭根（取最小值保证唯一性）。
• 家庭索引生成：用DENSE_RANK()生成连续的INDEX_FAMILY，统计总家庭数只需取MAX(index_family)即可。
• 父节点优先排序：通过parent_priority标记父节点（值为1），排序时优先排列。

示例输出 #

INDEX_FAMILY | NODE_ID | NODE_TYPE
------------|---------|----------
1           | P1      | 父节点
1           | P2      | 父节点
1           | C1      | 子节点
2           | P3      | 父节点
2           | C2      | 子节点
3           | P4      | 父节点
3           | P5      | 父节点
3           | C3      | 子节点

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