如何在MySQL/Databricks SQL中将横向层级表纵向展开？

方案1：SQL实现 #

假设原表名为hierarchy_table，直接通过UNION ALL枚举所有可能的父-子层级组合，同时过滤掉空值，最终排序输出：

SELECT
    Level0_id AS Parent,
    Level1_id AS Child,
    1 AS Level
FROM hierarchy_table
WHERE Level1_id IS NOT NULL

UNION ALL

SELECT
    Level0_id AS Parent,
    Level2_id AS Child,
    2 AS Level
FROM hierarchy_table
WHERE Level2_id IS NOT NULL

UNION ALL

SELECT
    Level0_id AS Parent,
    Level3_id AS Child,
    3 AS Level
FROM hierarchy_table
WHERE Level3_id IS NOT NULL

UNION ALL

SELECT
    Level0_id AS Parent,
    Level4_id AS Child,
    4 AS Level
FROM hierarchy_table
WHERE Level4_id IS NOT NULL

UNION ALL

SELECT
    Level0_id AS Parent,
    Level5_id AS Child,
    5 AS Level
FROM hierarchy_table
WHERE Level5_id IS NOT NULL

UNION ALL

SELECT
    Level1_id AS Parent,
    Level2_id AS Child,
    1 AS Level
FROM hierarchy_table
WHERE Level2_id IS NOT NULL

UNION ALL

SELECT
    Level1_id AS Parent,
    Level3_id AS Child,
    2 AS Level
FROM hierarchy_table
WHERE Level3_id IS NOT NULL

UNION ALL

SELECT
    Level1_id AS Parent,
    Level4_id AS Child,
    3 AS Level
FROM hierarchy_table
WHERE Level4_id IS NOT NULL

UNION ALL

SELECT
    Level1_id AS Parent,
    Level5_id AS Child,
    4 AS Level
FROM hierarchy_table
WHERE Level5_id IS NOT NULL

UNION ALL

SELECT
    Level2_id AS Parent,
    Level3_id AS Child,
    1 AS Level
FROM hierarchy_table
WHERE Level3_id IS NOT NULL

UNION ALL

SELECT
    Level2_id AS Parent,
    Level4_id AS Child,
    2 AS Level
FROM hierarchy_table
WHERE Level4_id IS NOT NULL

UNION ALL

SELECT
    Level2_id AS Parent,
    Level5_id AS Child,
    3 AS Level
FROM hierarchy_table
WHERE Level5_id IS NOT NULL

UNION ALL

SELECT
    Level3_id AS Parent,
    Level4_id AS Child,
    1 AS Level
FROM hierarchy_table
WHERE Level4_id IS NOT NULL

UNION ALL

SELECT
    Level3_id AS Parent,
    Level5_id AS Child,
    2 AS Level
FROM hierarchy_table
WHERE Level5_id IS NOT NULL

UNION ALL

SELECT
    Level4_id AS Parent,
    Level5_id AS Child,
    1 AS Level
FROM hierarchy_table
WHERE Level5_id IS NOT NULL

ORDER BY Parent, Level;

说明 #

• 每个SELECT块对应一组父层级到子层级的映射，同时指定相对层级数
• 通过WHERE子句过滤掉子层级为空的行，符合“某层级为null则后续跳过”的要求
• 最后通过ORDER BY按父ID和层级排序，输出整洁的结果

方案2：Python（Pandas）实现 #

如果用Python处理数据，可以通过遍历每行的非空层级值，生成所有父-子组合：

import pandas as pd

# 读取原数据（替换为实际读取方式，比如pd.read_csv）
data = {
    'Level0_id': [101, 101, 20],
    'Level1_id': [193, 193, 14],
    'Level2_id': [192, 191, 73],
    'Level3_id': [150, None, 45],
    'Level4_id': [201, None, None],
    'Level5_id': [300, None, None]
}
df = pd.DataFrame(data)

result = []

# 遍历每行数据
for _, row in df.iterrows():
    # 提取当前行的非空层级值，按顺序组成列表
    valid_levels = row.dropna().tolist()
    # 生成所有父-子配对
    for parent_idx in range(len(valid_levels)):
        parent = valid_levels[parent_idx]
        for child_idx in range(parent_idx + 1, len(valid_levels)):
            child = valid_levels[child_idx]
            # 相对层级为子索引与父索引的差值
            relative_level = child_idx - parent_idx
            result.append({
                'Parent': parent,
                'Child': child,
                'Level': relative_level
            })

# 转换为DataFrame并排序
result_df = pd.DataFrame(result).sort_values(by=['Parent', 'Level']).reset_index(drop=True)
print(result_df)

说明 #

• 先提取每行的非空层级值，自动截断空值部分
• 双重循环遍历所有可能的父-子组合，计算相对层级
• 最后整理结果并排序，完全匹配需求格式

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