如何用Python将三表联查结果按层级分组为嵌套字典？

有两种简便的方法可以完成这个层级分组转换，具体实现如下：

方法一：基于字典的遍历分组 #

这种方法通过字典快速定位已创建的订单和条目，遍历每条数据时逐步构建嵌套结构，逻辑直观，无需提前排序：

data = [
    {"service_order_number": "ABC", "item_id": 0, "ticket_id": 10},
    {"service_order_number": "ABC", "item_id": 0, "ticket_id": 11},
    {"service_order_number": "ABC", "item_id": 1, "ticket_id": 12},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 13},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 14},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 15}]

result = []
order_map = {}  # 用字典快速查找已存在的订单

for entry in data:
    order_num = entry["service_order_number"]
    item_id = entry["item_id"]
    ticket_id = entry["ticket_id"]
    
    # 处理订单层级
    if order_num not in order_map:
        order_obj = {"service_order_number": order_num, "line_items": []}
        order_map[order_num] = order_obj
        result.append(order_obj)
    order_obj = order_map[order_num]
    
    # 处理条目层级：临时构建item_id到item对象的映射
    item_map = {item["item_id"]: item for item in order_obj["line_items"]}
    if item_id not in item_map:
        item_obj = {"item_id": item_id, "tickets": []}
        order_obj["line_items"].append(item_obj)
        item_map[item_id] = item_obj
    item_obj = item_map[item_id]
    
    # 添加当前工单
    item_obj["tickets"].append({"ticket_id": ticket_id})

print(result)

方法二：使用itertools.groupby分组 #

利用Python标准库的itertools.groupby可以快速按指定键分组，不过需要先对数据按分组键排序（确保同组数据连续）：

from itertools import groupby

data = [
    {"service_order_number": "ABC", "item_id": 0, "ticket_id": 10},
    {"service_order_number": "ABC", "item_id": 0, "ticket_id": 11},
    {"service_order_number": "ABC", "item_id": 1, "ticket_id": 12},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 13},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 14},
    {"service_order_number": "DEF", "item_id": 3, "ticket_id": 15}]

# 先按service_order_number和item_id排序，保证groupby能正确分组
sorted_data = sorted(data, key=lambda x: (x["service_order_number"], x["item_id"]))

result = []
# 第一层按订单号分组
for order_num, order_group in groupby(sorted_data, key=lambda x: x["service_order_number"]):
    line_items = []
    # 第二层按条目ID分组
    for item_id, item_group in groupby(order_group, key=lambda x: x["item_id"]):
        # 收集当前条目下的所有工单
        tickets = [{"ticket_id": entry["ticket_id"]} for entry in item_group]
        line_items.append({"item_id": item_id, "tickets": tickets})
    result.append({"service_order_number": order_num, "line_items": line_items})

print(result)

方法对比 #

• 方法一：无需排序，遍历一次即可完成，适合数据量较大或原始数据无序的场景，查找效率高。
• 方法二：代码更简洁，依赖标准库，但需要先排序，适合数据本身已按分组键有序，或排序成本较低的情况。

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