在MySQL中使用Python对多表连接返回的数据进行分组

后端开发 2026-07-11

我的数据库包含治疗（treatment_id），这些治疗在一天中的不同时间给予患者（due_time）。我想在GUI中显示这个内容，每种治疗占一行，每个 due_time 将显示在一个代表时间线的24个水平盒子中的其中一个，顺序为00、01、02等等。大概是这样的：

               00   01   02   03   04   05 .... 21   22   23
Treatment1      x                        x            x

Treatment2                x              x

其中Treatment1的用药时间为00:00、05:00和 22:00，Treatment2的用药时间为02:00和 05:00。

查询从几张表中抓取数据：

SELECT
t.treatment_id,
p.patient_id,
t.treatment,
HOUR(ts.due_time) as due_time,
ts.status,
ts.ts_id

FROM
    tbl_treatments_schedule ts
left JOIN
    tbl_treatments t
    ON (t.treatment_id = ts.fk_treatment_id)

left join
tbl_patients p
ON (p.patient_id = t.fk_patient_id)

WHERE ts.due_date = q_due_date AND p.name = q_name;

返回的数据目前看起来是这样的：

Cols in DB
treatment_id|patient_id|treatment|due_time|status|treatment_schedule_id

[
(13, 91,'co-amox 2.3ml IV', 16, 0, 1), 
(13, 91,'co-amox 2.3ml IV', 14, 0, 3), 
(13, 91,'co-amox 2.3ml IV', 12, 0, 4), 
(13, 91,'co-amox 2.3ml IV', 13, 0, 9), 
(13, 91,'co-amox 2.3ml IV', 6, 0, 10), 
(13, 91,'co-amox 2.3ml IV', 4, 0, 11), 
(13, 91,'co-amox 2.3ml IV', 22, 0, 12), 
(13, 91,'co-amox 2.3ml IV', 18, 0, 13), 
(14, 91,'metacam 4.8kg dose PO SID', 22, 0, 14), 
(14, 91,'metacam 4.8kg dose PO SID', 1, 0, 15), 
(14, 91,'metacam 4.8kg dose PO SID', 7, 0, 16), 
(14, 91,'metacam 4.8kg dose PO SID', 6, 0, 17)]
]

我想把这些数据处理成按 treatment_id（例如13）和 treatment 分组的形式。这样我就可以创建一个标题为例："co-amox 2.3ml IV" 的单行，然后遍历各种 due_time 值，并在上面描述的水平时间线中显示它们。

下面是我目前的一个最小可运行示例：

data = [(13, 91, 'co-amox 2.3ml IV', 16, 1), (13, 91, 'co-amox 2.3ml IV', 14, 3), (13, 91, 'co-amox 2.3ml IV', 12, 4), (13, 91, 'co-amox 2.3ml IV', 13, 9), (13, 91, 'co-amox 2.3ml IV', 6, 10), (13, 91, 'co-amox 2.3ml IV', 4, 11), (13, 91, 'co-amox 2.3ml IV', 22, 12), (13, 91, 'co-amox 2.3ml IV', 18, 13), (14, 91, 'metacam 4.8kg dose PO SID', 22, 14), (14, 91, 'metacam 4.8kg dose PO SID', 1, 15), (14, 91, 'metacam 4.8kg dose PO SID', 7, 16), (14, 91, 'metacam 4.8kg dose PO SID', 6, 17)]


def group_rows_by_key(data):
    grouped_data = {}
    for item in data:
        #loop over initial db results (lots of repeated treatment id rows)
        #get id we want to group around e.g. 33
        key = item[0]

        #record associated with this key 
        value = item 

        if key in grouped_data:
            #if treatment id key, add this row to it
            grouped_data[key].append(value)
        else:
            #if no treatment key, then create one, and give it a value of the entire row that contains it
            grouped_data[key] = [value]

    return grouped_data

parsed_data = group_rows_by_key(data)

#below grabs the treatment name value[0][2] to store as 'standalone' single element in list
#needed for row title field corresponding to one-or-more treatments_schedules 8am 10am etc
for key, value in parsed_data.items():
    parsed_data[key] = [value[0][2],value] 

print(parsed_data)

这会得到如下结果：

{13: 
['co-amox 2.3ml IV', [
(13, 91, 'co-amox 2.3ml IV', 16, 1), 
(13, 91, 'co-amox 2.3ml IV', 14, 3), 
(13, 91, 'co-amox 2.3ml IV', 12, 4), 
(13, 91, 'co-amox 2.3ml IV', 13, 9), 
(13, 91, 'co-amox 2.3ml IV', 6, 10), 
(13, 91, 'co-amox 2.3ml IV', 4, 11), 
(13, 91, 'co-amox 2.3ml IV', 22, 12), 
(13, 91, 'co-amox 2.3ml IV', 18, 13)]
], 

14: ['metacam 4.8kg dose PO SID', [
(14, 91, 'metacam 4.8kg dose PO SID', 22, 14), 
(14, 91, 'metacam 4.8kg dose PO SID', 1, 15), 
(14, 91, 'metacam 4.8kg dose PO SID', 7, 16), 
(14, 91, 'metacam 4.8kg dose PO SID', 6, 17)]
]
}

工作还算可以，但显得有点笨拙。我在想是否有更好的实现方式。在一个理想的世界里，应该只保留必需的值并以一个简单的列表呈现，大致如下：

[13, 'co-amox 2.3ml IV', [ (16,1),(14,3),(12,4),(13,9),(6,10),(4,11),(22,12),(18,13) ]]

[14, 'metacam 4.8kg dose PO SID', [ (22,14), (1,15), (7,16), (6,17) ]]

我在想是不是应该向数据库发出多次请求，以便得到恰好符合我期望的结果集，但这取决于 treatment_id 的数量（以及 due_time 的数量），因此我一直努力将其控制在一个 SELECT，再对结果集进行处理。

解决方案

我在想是不是应该向数据库发出多次请求，以便得到恰好符合我期望的结果集，但这取决于treatment_ids（以及due_times）的数量，因此我一直努力将其控制在一次SELECT，然后再对结果集进行处理。

通过在单次数据库查询中使用JSON函数来获取并分组数据，让数据库来完成它擅长的工作（而不是把全部数据拉到Python中再重组）：

SELECT JSON_ARRAY(
       treatment_id,
       patient_id,
       treatment,
       status,
       JSON_ARRAYAGG(JSON_ARRAY(due_time, treatment_schedule_id))
       ) AS treatment
FROM   treatments
GROUP BY
       patient_id,
       treatment_id,
       treatment,
       status;

对于示例数据：

CREATE TABLE treatments (
  treatment_id INT,
  patient_id   INT,
  treatment    VARCHAR(50),
  due_time     INT,
  status       INT,
  treatment_schedule_id INT
);

INSERT INTO treatments (treatment_id, patient_id, treatment, due_time, status, treatment_schedule_id)
VALUES (13, 91,'co-amox 2.3ml IV', 16, 0, 1), 
(13, 91,'co-amox 2.3ml IV', 14, 0, 3), 
(13, 91,'co-amox 2.3ml IV', 12, 0, 4), 
(13, 91,'co-amox 2.3ml IV', 13, 0, 9), 
(13, 91,'co-amox 2.3ml IV', 6, 0, 10), 
(13, 91,'co-amox 2.3ml IV', 4, 0, 11), 
(13, 91,'co-amox 2.3ml IV', 22, 0, 12), 
(13, 91,'co-amox 2.3ml IV', 18, 0, 13), 
(14, 91,'metacam 4.8kg dose PO SID', 22, 0, 14), 
(14, 91,'metacam 4.8kg dose PO SID', 1, 0, 15), 
(14, 91,'metacam 4.8kg dose PO SID', 7, 0, 16), 
(14, 91,'metacam 4.8kg dose PO SID', 6, 0, 17);

输出结果为：

treatment
[13, 91, "co-amox 2.3ml IV", 0, [[16, 1], [14, 3], [12, 4], [13, 9], [6, 10], [4, 11], [22, 12], [18, 13]]]
[14, 91, "metacam 4.8kg dose PO SID", 0, [[22, 14], [1, 15], [7, 16], [6, 17]]]

或者，如果你想把数据作为单独的列来展示，你可以使用条件聚合：

SELECT treatment_id,
       patient_id,
       treatment,
       status,
       MAX(CASE due_time WHEN  0 THEN treatment_schedule_id END) AS "00",
       MAX(CASE due_time WHEN  1 THEN treatment_schedule_id END) AS "01",
       MAX(CASE due_time WHEN  2 THEN treatment_schedule_id END) AS "02",
       MAX(CASE due_time WHEN  3 THEN treatment_schedule_id END) AS "03",
       MAX(CASE due_time WHEN  4 THEN treatment_schedule_id END) AS "04",
       MAX(CASE due_time WHEN  5 THEN treatment_schedule_id END) AS "05",
       MAX(CASE due_time WHEN  6 THEN treatment_schedule_id END) AS "06",
       MAX(CASE due_time WHEN  7 THEN treatment_schedule_id END) AS "07",
       MAX(CASE due_time WHEN  8 THEN treatment_schedule_id END) AS "08",
       MAX(CASE due_time WHEN  9 THEN treatment_schedule_id END) AS "09",
       MAX(CASE due_time WHEN 10 THEN treatment_schedule_id END) AS "10",
       MAX(CASE due_time WHEN 11 THEN treatment_schedule_id END) AS "11",
       MAX(CASE due_time WHEN 12 THEN treatment_schedule_id END) AS "12",
       MAX(CASE due_time WHEN 13 THEN treatment_schedule_id END) AS "13",
       MAX(CASE due_time WHEN 14 THEN treatment_schedule_id END) AS "14",
       MAX(CASE due_time WHEN 15 THEN treatment_schedule_id END) AS "15",
       MAX(CASE due_time WHEN 16 THEN treatment_schedule_id END) AS "16",
       MAX(CASE due_time WHEN 17 THEN treatment_schedule_id END) AS "17",
       MAX(CASE due_time WHEN 18 THEN treatment_schedule_id END) AS "18",
       MAX(CASE due_time WHEN 19 THEN treatment_schedule_id END) AS "19",
       MAX(CASE due_time WHEN 20 THEN treatment_schedule_id END) AS "20",
       MAX(CASE due_time WHEN 21 THEN treatment_schedule_id END) AS "21",
       MAX(CASE due_time WHEN 22 THEN treatment_schedule_id END) AS "22",
       MAX(CASE due_time WHEN 23 THEN treatment_schedule_id END) AS "23"
FROM   treatments
GROUP BY
       patient_id,
       treatment_id,
       treatment,
       status;

输出为：

treatment_id patient_id treatment status 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
13 91 co-amox 2.3ml IV 0 null null null null 11 null 10 null null null null null 4 9 3 null 1 null 13 null null null 12 null
14 91 metacam 4.8kg dose PO SID 0 null 15 null null null null 17 16 null null null null null null null null null null null null null null 14 null

然后你就可以用Python来显示它。

示例

站内所有文章版权归属LeftHeroAI导航站，无授权禁止任何主体转载、抄袭、复制内容，亦不得私自架设镜像站点。一经侵权，本站将通过法律途径追责。

相关文章

如何通过Python/DBus在 KDE Plasma 6（Wayland）中获取活动窗口标题？
3
怎么用Shapely检测共享边
5
Python使用带有巴西葡萄牙语字符（ç、á、é、à、í）的pyxtxt.xtxt（文件名）
4
从C++调用的Python函数未定义
0
用Jinja2代替正则表达式
1
在使用Playwright设置Langchain代理时遇到的问题
3
汇集全球数千款 AI 工具，每日持续更新，配套 AI 开发学习资源，轻松用 AI 提升工作效率。快捷键 Ctrl+D/⌘+D 一键收藏本站。

超人AI导航关于我们免责声明

Copyright © 超人AI导航  辽ICP备2024029316号-4