SQL自引用层级表层级数据转列的递归实现方案咨询

实现思路 #

核心分两步实现无手动左连接的层级行转列：

1. 用递归CTE遍历所有层级节点，同时记录每个节点的层级深度、对应层级的车辆ID和名称，以及完整路径
2. 通过聚合函数按路径分组提取各层级字段值，无需手动编写多层左连接逻辑

适配MS SQL的可运行代码 #

-- 示例基础表结构，可根据实际业务调整字段类型
CREATE TABLE vehicle (
    vehicle_id INT PRIMARY KEY,
    vehicle_name VARCHAR(100) NOT NULL,
    parent_vehicle_id INT NULL FOREIGN KEY REFERENCES vehicle(vehicle_id)
);

-- MS SQL环境直接去掉RECURSIVE关键字即可运行，其他SQL引擎保留即可通用
WITH vehicle_hierarchy AS (
    -- 锚点成员：取顶层根节点
    SELECT 
        vehicle_id,
        vehicle_name,
        parent_vehicle_id,
        1 AS level_depth,
        CAST(vehicle_id AS VARCHAR(1000)) AS id_path,
        CAST(vehicle_name AS VARCHAR(1000)) AS name_path,
        vehicle_id AS level1_id,
        vehicle_name AS level1_name,
        CAST(NULL AS INT) AS level2_id,
        CAST(NULL AS VARCHAR(100)) AS level2_name,
        CAST(NULL AS INT) AS level3_id,
        CAST(NULL AS VARCHAR(100)) AS level3_name,
        CAST(NULL AS INT) AS level4_id,
        CAST(NULL AS VARCHAR(100)) AS level4_name
        -- 可根据实际业务最大层级，提前扩展对应层级字段
    FROM vehicle
    WHERE parent_vehicle_id IS NULL

    UNION ALL

    -- 递归成员：逐层遍历子节点，自动填充对应层级字段
    SELECT 
        v.vehicle_id,
        v.vehicle_name,
        v.parent_vehicle_id,
        vh.level_depth + 1 AS level_depth,
        CAST(CONCAT(vh.id_path, '/', v.vehicle_id) AS VARCHAR(1000)) AS id_path,
        CAST(CONCAT(vh.name_path, '/', v.vehicle_name) AS VARCHAR(1000)) AS name_path,
        vh.level1_id,
        vh.level1_name,
        CASE WHEN vh.level_depth + 1 = 2 THEN v.vehicle_id ELSE vh.level2_id END AS level2_id,
        CASE WHEN vh.level_depth + 1 = 2 THEN v.vehicle_name ELSE vh.level2_name END AS level2_name,
        CASE WHEN vh.level_depth + 1 = 3 THEN v.vehicle_id ELSE vh.level3_id END AS level3_id,
        CASE WHEN vh.level_depth + 1 = 3 THEN v.vehicle_name ELSE vh.level3_name END AS level3_name,
        CASE WHEN vh.level_depth + 1 = 4 THEN v.vehicle_id ELSE vh.level4_id END AS level4_id,
        CASE WHEN vh.level_depth + 1 = 4 THEN v.vehicle_name ELSE vh.level4_name END AS level4_name
        -- 与锚点成员的层级字段对应扩展即可
    FROM vehicle v
    INNER JOIN vehicle_hierarchy vh ON v.parent_vehicle_id = vh.vehicle_id
)
-- 最终输出全路径层级结构，每行对应一条根节点到子节点的完整链路
SELECT 
    MAX(level1_id) AS level1_id,
    MAX(level1_name) AS level1_name,
    MAX(level2_id) AS level2_id,
    MAX(level2_name) AS level2_name,
    MAX(level3_id) AS level3_id,
    MAX(level3_name) AS level3_name,
    MAX(level4_id) AS level4_id,
    MAX(level4_name) AS level4_name,
    id_path,
    name_path
FROM vehicle_hierarchy
GROUP BY id_path, name_path
ORDER BY id_path;

适配说明 #

• 若需要每个节点都展示自身所在的全层级结构，直接去掉最后的GROUP BY语句，查询vehicle_hierarchy全表即可
• 代码核心逻辑为标准SQL语法，仅RECURSIVE关键字在MS SQL中需要省略，其余逻辑适配MySQL 8.0+、PostgreSQL、Oracle 11g+等主流引擎，满足跨引擎需求
• 无需手动编写多层左连接，仅需根据业务实际最大层级，在锚点和递归成员中新增对应层级字段即可，维护成本远低于手动左连接方案
• 若层级不固定，可搭配MS SQL动态SQL，先查询实际最大层级，再自动生成对应层级的字段语句执行即可

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