请求协助编写PostgreSQL层级数据查询语句（指定Schema）

数据库Schema #

CREATE TABLE manufacturing_process (
    id              bigserial PRIMARY KEY,
    process_type    manufacturing_process_type NOT NULL,
    name            VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE manufacturing_process_group (
    record_id       bigserial UNIQUE,
    group_id        bigint NOT NULL,
    title           VARCHAR(255) NOT NULL,
    parent_id       bigint,
    child_ids       bigint[],
    expansion_type  manufacturing_process_expansion_type NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES manufacturing_process(id)
);

样本数据集 #

manufacturing_process（mp） #

manufacturing_process_group（mpg） #

查询需求 #

针对指定的group_id，获取包含以下字段的层级结构结果：

• mp.id：制造流程ID
• mp.name：制造流程名称
• parent_id：父节点ID（顶级分组对应父ID为null，其下子节点父ID为0）
• mpg.title：分组标题（仅分组节点有值）
• mpg.expansion_type：分组展开类型（仅分组节点有值）

查询语句 #

使用递归CTE实现层级遍历，处理分组节点和子流程节点的关联：

WITH RECURSIVE group_hierarchy AS (
    -- 初始化：处理顶级分组（parent_id为NULL的分组）
    SELECT
        0::bigint AS mp_id,
        NULL::varchar AS mp_name,
        NULL::bigint AS parent_id,
        mpg.title,
        mpg.expansion_type,
        mpg.child_ids,
        mpg.parent_id AS group_parent_id,
        ARRAY[mpg.record_id] AS path -- 记录路径用于排序
    FROM manufacturing_process_group mpg
    WHERE mpg.group_id = 1 -- 替换为目标group_id
      AND mpg.parent_id IS NULL

    UNION ALL

    -- 递归处理：拆分child_ids为单个流程ID，同时关联对应的子分组
    SELECT
        unnest(mpg.child_ids) AS mp_id,
        mp.name AS mp_name,
        -- 确定当前节点的父ID：顶级分组的子节点父ID为0，否则为分组的parent_id
        CASE WHEN gh.mp_id = 0 THEN 0 ELSE gh.mp_id END AS parent_id,
        -- 仅当前流程对应子分组时填充标题和类型
        sub_mpg.title,
        sub_mpg.expansion_type,
        sub_mpg.child_ids,
        sub_mpg.parent_id AS group_parent_id,
        gh.path || sub_mpg.record_id AS path
    FROM group_hierarchy gh
    CROSS JOIN UNNEST(gh.child_ids) AS child_id
    LEFT JOIN manufacturing_process mp ON mp.id = child_id
    LEFT JOIN manufacturing_process_group sub_mpg 
        ON sub_mpg.parent_id = child_id 
        AND sub_mpg.group_id = 1 -- 匹配目标group_id
    WHERE gh.child_ids IS NOT NULL
)
-- 最终查询：整理结果并去重，按层级顺序输出
SELECT
    mp_id AS "mp.id",
    mp_name AS "mp.name",
    parent_id,
    title AS "mpg.title",
    expansion_type AS "mpg.expansion_type"
FROM group_hierarchy
UNION ALL
SELECT
    mp.id AS "mp.id",
    mp.name AS "mp.name",
    gh.mp_id AS parent_id,
    NULL AS "mpg.title",
    NULL AS "mpg.expansion_type"
FROM group_hierarchy gh
CROSS JOIN UNNEST(gh.child_ids) AS child_id
JOIN manufacturing_process mp ON mp.id = child_id
WHERE gh.title IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM group_hierarchy gh2 
      WHERE gh2.mp_id = mp.id 
        AND gh2.parent_id = gh.mp_id
  )
ORDER BY path, "mp.id";

验证结果 #

执行上述查询（指定group_id=1），将得到与预期一致的结果：

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