如何用Postgres递归查询和窗口函数生成订单表层级树形结构

以下实现基于PostgreSQL语法，若使用其他SQL方言可对应调整语法细节：

实现步骤 #

步骤1：新增parent字段 #

ALTER TABLE ORDERS ADD COLUMN parent INT NULL;

步骤2：创建指定序列 #

CREATE SEQUENCE seq_orders START WITH 1 INCREMENT BY 1;

步骤3：插入层级节点并关联parent #

3.1 插入一级分类节点（顶级节点parent为null）

INSERT INTO ORDERS (id, description, parent)
SELECT nextval('seq_orders'), category, NULL
FROM (SELECT DISTINCT category FROM ORDERS) distinct_cats;

3.2 插入二级子分类节点，parent关联对应一级分类ID

WITH cat_level AS (
    -- 拿到所有一级分类的ID和名称
    SELECT id AS cat_id, description AS cat_name
    FROM ORDERS
    WHERE parent IS NULL
)
INSERT INTO ORDERS (id, description, parent)
SELECT nextval('seq_orders'), subcategory, cat_id
FROM (
    SELECT DISTINCT o.category, o.subcategory, c.cat_id
    FROM ORDERS o
    JOIN cat_level c ON o.category = c.cat_name
    WHERE o.id >= 1000 -- 过滤刚插入的一级节点，仅用原始业务数据去重子分类
) distinct_subcats;

3.3 更新原始业务行（三级描述节点）的parent关联对应二级子分类ID

WITH subcat_level AS (
    -- 拿到所有二级子分类的ID、名称、所属一级分类名称
    SELECT s.id AS subcat_id, s.description AS subcat_name, p.description AS cat_name
    FROM ORDERS s
    JOIN ORDERS p ON s.parent = p.id
    WHERE p.parent IS NULL
)
UPDATE ORDERS o
SET parent = sl.subcat_id
FROM subcat_level sl
WHERE o.category = sl.cat_name 
  AND o.subcategory = sl.subcat_name
  AND o.id >= 1000; -- 仅更新原始业务数据行

步骤4：删除冗余字段 #

ALTER TABLE ORDERS DROP COLUMN category, DROP COLUMN subcategory;

递归查询生成树形展示结果 #

使用窗口函数控制排序、递归CTE生成层级缩进：

WITH RECURSIVE order_tree AS (
    -- 锚点：查询顶级分类节点
    SELECT
        id,
        description,
        parent,
        0 AS level,
        -- 构造排序路径，保证树形顺序正确
        ARRAY[ROW_NUMBER() OVER(ORDER BY description)] AS sort_path
    FROM ORDERS
    WHERE parent IS NULL

    UNION ALL

    -- 递归：查询子节点
    SELECT
        o.id,
        o.description,
        o.parent,
        ot.level + 1 AS level,
        ot.sort_path || ROW_NUMBER() OVER(PARTITION BY o.parent ORDER BY o.description)
    FROM ORDERS o
    JOIN order_tree ot ON o.parent = ot.id
)
-- 按层级拼接缩进符号输出结果
SELECT
    CASE level
        WHEN 0 THEN description
        WHEN 1 THEN '  -> ' || description
        WHEN 2 THEN '    -> ' || description
    END AS result
FROM order_tree
ORDER BY sort_path;

执行后输出结果完全匹配需求的树形结构。

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