如何将SQL查询结果按产品分组，合并投票字段为列表

方法一：Python端分组处理 #

用字典按(product, model, creator)作为唯一键分组，收集对应vote值：

# 执行查询
cursor_test.execute("SELECT product, model, creator, vote FROM sales")

# 初始化分组字典
result_dict = {}
for product, model, creator, vote in cursor_test.fetchall():
    key = (product, model, creator)
    if key not in result_dict:
        result_dict[key] = []
    result_dict[key].append(vote)

# 转换为目标格式输出
for key, votes in result_dict.items():
    print( (key[0], key[1], key[2], votes) )

运行后输出：

('iMac 24', '24', 'Apple', [8, 9, 10])
('HP 24', '24-Cb1033nl', 'HP', [7, 6, 7])

方法二：SQL端直接聚合 #

根据你使用的数据库类型，用对应聚合函数在查询阶段完成合并：

MySQL/MariaDB #

使用GROUP_CONCAT拼接vote值，再在Python中转成列表：

SELECT product, model, creator, GROUP_CONCAT(vote SEPARATOR ',') AS votes
FROM sales
GROUP BY product, model, creator

Python处理代码：

cursor_test.execute("""
    SELECT product, model, creator, GROUP_CONCAT(vote SEPARATOR ',') AS votes
    FROM sales
    GROUP BY product, model, creator
""")
for row in cursor_test.fetchall():
    product, model, creator, votes_str = row
    votes = list(map(int, votes_str.split(',')))
    print( (product, model, creator, votes) )

PostgreSQL #

使用array_agg直接返回数组，Python中可直接转为列表：

SELECT product, model, creator, array_agg(vote) AS votes
FROM sales
GROUP BY product, model, creator

Python处理代码：

cursor_test.execute("""
    SELECT product, model, creator, array_agg(vote) AS votes
    FROM sales
    GROUP BY product, model, creator
""")
for row in cursor_test.fetchall():
    print(row)

SQLite #

同样用GROUP_CONCAT，处理逻辑和MySQL一致：

SELECT product, model, creator, GROUP_CONCAT(vote) AS votes
FROM sales
GROUP BY product, model, creator

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