如何从层级化数据库表中最优查询并组装树形结构？

问题背景 #

现有一张按层级（First→Second→Third）组织数据的表，结构及示例数据如下：

+----+-------+--------+-------+
| Id | First | Second | Third |
+----+-------+--------+-------+
| 1  |   1   |   1    |   1   |
| 2  |   1   |   1    |   2   |
| 3  |   1   |   1    |   2   |
| 4  |   1   |   2    |   1   |
| 5  |   1   |   2    |   2   |
| 6  |   1   |   2    |   3   |
...
+----+-------+--------+-------+

需要将数据转换为三级嵌套的树形结构，最终格式如下：

{ 
    "1": {                   // First 节点
        "1": {               // Second 节点
            "1": [ 1 ],      // Third 节点对应的 Id 列表
            "2": [ 2, 3 ]              
        },
        "2": {
            "1": [ 4 ],
            "2": [ 5 ],
            "3": [ 6 ]
        }  
    }
}

核心需求：获取这类数据的最优处理方案，同时需要数据库分组查询和代码端组装树形结构的具体示例。

解决方案 #

方案一：数据库端分组聚合查询 #

利用数据库的聚合函数直接按层级分组，提前聚合Id列表，减少后端数据处理量。以MySQL为例，使用GROUP_CONCAT完成Id聚合：

SELECT 
    First,
    Second,
    Third,
    GROUP_CONCAT(Id SEPARATOR ',') AS id_list
FROM your_table_name
GROUP BY First, Second, Third
ORDER BY First, Second, Third;

查询结果结构如下：

+-------+--------+-------+---------+
| First | Second | Third | id_list |
+-------+--------+-------+---------+
| 1     | 1      | 1     | 1       |
| 1     | 1      | 2     | 2,3     |
| 1     | 2      | 1     | 4       |
| 1     | 2      | 2     | 5       |
| 1     | 2      | 3     | 6       |
...
+-------+--------+-------+---------+

这种方式的优势是数据库直接完成分组聚合，后端只需将id_list拆分为数组后，快速组装成树形结构，性能更优。

方案二：代码端读取原始数据后组装树形结构 #

如果数据库不支持复杂聚合，或者需要更灵活的结构控制，可以先读取全量原始数据，再通过代码嵌套分组。以下是Python的示例代码：

# 模拟从数据库获取的原始数据列表
raw_data = [
    {"Id": 1, "First": 1, "Second": 1, "Third": 1},
    {"Id": 2, "First": 1, "Second": 1, "Third": 2},
    {"Id": 3, "First": 1, "Second": 1, "Third": 2},
    {"Id": 4, "First": 1, "Second": 2, "Third": 1},
    {"Id": 5, "First": 1, "Second": 2, "Third": 2},
    {"Id": 6, "First": 1, "Second": 2, "Third": 3},
]

tree = {}

for item in raw_data:
    first_key = str(item["First"])
    second_key = str(item["Second"])
    third_key = str(item["Third"])
    id_val = item["Id"]
    
    # 逐层初始化嵌套结构
    if first_key not in tree:
        tree[first_key] = {}
    if second_key not in tree[first_key]:
        tree[first_key][second_key] = {}
    if third_key not in tree[first_key][second_key]:
        tree[first_key][second_key][third_key] = []
    
    # 将Id添加到对应层级的列表中
    tree[first_key][second_key][third_key].append(id_val)

# 输出格式化后的树形结构
import json
print(json.dumps(tree, indent=4))

运行后输出结构与目标完全一致。其他语言（如Java、JavaScript）的实现逻辑类似：通过多层哈希表/对象嵌套，遍历原始数据逐层填充即可。

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