嵌套集数据库层级关联：将信号与全层级路径合并为行

数据库简化结构 #

1. 根表Bus（B） #

2. 层级结构表Compound（C） #

各层级属性不同：

• 层级1（C1）：

• 层级2（C2a）：

• 层级2（C2b）：

3. 信号表S（S1为ID） #

需求说明 #

生成包含S表10条数据的列表，每条数据需附带完整的层级路径及向上遍历的层级值，预期结果格式如下：

现有问题 #

之前尝试的SQL语句不仅导致机器崩溃，且仅支持固定3层级（cBase、cPdu、cSwp），无法适配任意层级的结构：

select
    *
from
    signal s,
    compound c 
join (
    select
        bus.short_name,
        cBase.dtype,
        cBase.value,
        cPdu.relative_bit_position,
        cPdu.bit_length,
        cPdu.left_signal,
        cPdu.right_signal,
        cSwp.switch_code
    from
        compound as cbase
    left join compound as cPdu on
        cPdu.dtype = 'P'
        and cBase.left_compound < cPdu.left_compound
        and cBase.right_compound > cPdu.right_compound
    left join compound as cswp on
        cSwp.dtype = 'SWP'
        and cBase.left_compound < cSwp.left_compound
        and cBase.right_compound > cSwp.right_compound
    left join bus as bus on
        bus.left_compound < cBase.left_compound
        and bus.right_compound > cBase.right_compound
    where
        cBase.dType = 'CF') as res on s.id between res.left_signal and res.right_signal

通用解决方案 #

针对嵌套集结构的任意层级遍历，推荐使用**递归CTE（公共表表达式）**实现，它能高效遍历所有层级关系，无需硬编码固定层级数。

步骤1：递归遍历Compound层级，获取完整路径 #

用递归CTE获取每个Compound节点的所有祖先节点，构建完整层级路径：

WITH RECURSIVE compound_hierarchy AS (
    -- 锚点成员：最顶层节点（CL=1，对应C1）
    SELECT 
        CL, CR, SL, SR, C5, C6,
        CAST(CONCAT('C1: ', C6) AS VARCHAR(1000)) AS level_path,
        1 AS level_depth
    FROM Compound
    WHERE CL = 1

    UNION ALL

    -- 递归成员：遍历子节点（子节点CL>父CL且CR<父CR）
    SELECT 
        c.CL, c.CR, c.SL, c.SR, c.C5, c.C6,
        CONCAT(ch.level_path, ' > ', CONCAT('C', ch.level_depth+1, ': ', COALESCE(c.C5, c.C6))),
        ch.level_depth + 1
    FROM Compound c
    JOIN compound_hierarchy ch ON c.CL > ch.CL AND c.CR < ch.CR
),
-- 关联根表Bus
full_hierarchy AS (
    SELECT 
        bh.level_path,
        bh.level_depth,
        bh.CL, bh.CR, bh.SL, bh.SR, bh.C5, bh.C6,
        b.CL AS bus_CL, b.CR AS bus_CR, b.SL AS bus_SL, b.SR AS bus_SR
    FROM compound_hierarchy bh
    JOIN Bus b ON bh.SL >= b.SL AND bh.SR <= b.SR
)

步骤2：关联信号表S，生成最终结果 #

将递归得到的层级数据与信号表关联，筛选出S表中属于当前层级范围（S1在SL和SR之间）的记录：

SELECT 
    s.S2 AS "S",
    fh.level_path AS "层级路径",
    CONCAT('C2: ', fh.C5) AS "C2-values",
    CONCAT('C1: ', fh.C6) AS "C1-values",
    'R' AS "R"
FROM full_hierarchy fh
JOIN Signal s ON s.S1 BETWEEN fh.SL AND fh.SR
LIMIT 10;

动态列适配说明 #

如果需要动态生成对应层级的列（而非固定展示C1、C2），可根据数据库类型用聚合函数将层级属性打包成结构化格式，例如PostgreSQL中用json_agg转为JSON对象：

SELECT 
    s.S2 AS "S",
    json_agg(json_build_object(
        CONCAT('C', fh.level_depth), 
        COALESCE(fh.C5, fh.C6)
    )) AS "层级属性",
    'R' AS "R"
FROM full_hierarchy fh
JOIN Signal s ON s.S1 BETWEEN fh.SL AND fh.SR
GROUP BY s.S1, s.S2
LIMIT 10;

性能优化建议 #

1. 为Compound表的CL、CR列创建联合索引，加速递归查询的关联匹配。
2. 为Signal表的S1列创建索引，提升范围匹配效率。
3. 避免SELECT *，只查询需要的字段，减少数据传输量。

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