MySQL 在一个公共列上加入 3 个表并修改列

我正在使用 Mysql -Workbench 和 MariaDB 数据库我有这三个表，其中只有公共列work_number：

    create table packing_record(
`id_pack` int(6) auto_increment NOT NULL primary Key,
`work_number` varchar(12),
`pick_basket` varchar(30),
`boxes` varchar(5),
`start_pack` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`finish_pack` datetime default Null);

Create Table work_time(
`id_record` int(6) auto_increment Not Null primary Key,
`work_number` varchar(12),
`position` varchar(30),
`start_work` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`finish_work` datetime default Null);

Create Table pause_time(
`id_pause` int(6) auto_increment Not Null primary Key,
`work_number` varchar(12),
`reason` varchar(100),
`start_pause` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`end_pause` datetime default Null);

我想修改它们work_time，pause_time因此它按 work_number 和日期对它们进行分组，此外它还会创建自定义列，其中是work_time全天的总时间和pause_time表的总暂停时间。从第一个表开始，只有 SUM( boxes) 和按日期和 再次分组的打包时间work_number。总工作时间：

  select date_format(start_work,'%d-%m-%Y') as "Date", work_number as "Work Number", position as "Position", 
    IF(timediff(finish_work,start_work) > '00:00:00', timediff(finish_work,start_work), IF (time(finish_work) is NULL,timediff(current_timestamp,start_work), '') )
    AS 'Worked Time' from work_time
    group by date_format(start_work,'%d-%m-%Y'), work_number;

总暂停时间：

select date_format(start_pause,'%d-%m-%Y') as "Date", work_number as "Work Number", reason as "Pause Reason", IF(timediff(end_pause,start_pause) > '00:00:00', 
timediff(end_pause,start_pause), IF (time(end_pause) is NULL,timediff(current_timestamp,start_pause), '') )  AS 'Pause time' from pause_time
group by date_format(start_pause,'%d-%m-%Y'), work_number;

总打包订单：

select date_format(start_pack,'%d-%m-%Y') as "Date", work_number as "Work Number", sum(boxes) as "Boxes", 
time_format(SEC_TO_TIME( SUM( TIME_TO_SEC(IF(timediff(finish_pack,start_pack) > '00:00:00', 
timediff(finish_pack,start_pack), '')))),'%H:%i:%s')AS 'Total Time' from packing_record
group by date_format(start_pack,'%d-%m-%Y'), work_number;

我知道如何分别处理这些部分，但我想知道是否可以在一个查询中将它们全部连接在一起？编辑：我做了这个查询，但它不是正确的 SUM Boxes 列，看起来它是值的三倍。

 select date_format(pr.start_pack,'%d-%m-%Y') as "Date", ul.work_number as "Work Number", 
ul.worker_name as "Name", wt.position as "Position", sum(pr.boxes) as "Orders", 
time_format(SEC_TO_TIME( SUM( TIME_TO_SEC(IF(timediff(pr.finish_pack,pr.start_pack) > '00:00:00', 
timediff(pr.finish_pack,pr.start_pack), '')))),'%H:%i:%s')AS 'Total Packing Time', 
IF(timediff(wt.finish_work,wt.start_work) > '00:00:00', timediff(wt.finish_work,wt.start_work), 
    IF (time(wt.finish_work) is NULL,timediff(current_timestamp,wt.start_work), '') ) AS 'Time at Pack Station',
IF(timediff(pt.end_pause,pt.start_pause) > '00:00:00', (time_format(SEC_TO_TIME( SUM( TIME_TO_SEC(timediff(pt.end_pause,pt.start_pause)))), '%H:%i:%s')),
         IF (time(pt.end_pause) is NULL, timediff(current_timestamp,pt.start_pause), '') )  AS 'Pause Total',
time_format(timediff(IF(timediff(wt.finish_work,wt.start_work) > '00:00:00', timediff(wt.finish_work,wt.start_work), 
    IF (time(wt.finish_work) is NULL,timediff(current_timestamp,wt.start_work), '') ),IF(timediff(pt.end_pause,pt.start_pause) > '00:00:00', 
        (time_format(SEC_TO_TIME( SUM( TIME_TO_SEC(timediff(pt.end_pause,pt.start_pause)))), '%H:%i:%s')),
         IF (time(pt.end_pause) is NULL, timediff(current_timestamp,pt.start_pause), '') ) ), '%H:%i:%s') as "Worked Time"

from user_logins ul, packing_record pr, work_time wt, pause_time pt
where ul.work_number = pr.work_number and ul.work_number = wt.work_number and ul.work_number = pt.work_number 
group by ul.work_number, Date;

编辑：所以这个解决了我的问题：

  select date_format(pr.start_pack,'%d-%m-%Y') as 'Date', ul.work_number as 'Work Number', 
    ul.worker_name as 'Name', sum(distinct pr.boxes) as 'Orders', 
        time_format(SEC_TO_TIME( SUM(distinct TIME_TO_SEC(IF(timediff(pr.finish_pack,pr.start_pack) > '00:00:00', 
        timediff(pr.finish_pack,pr.start_pack), '')))),'%H:%i:%s')AS 'Total Packing Time', 
    time_format(min(wt.start_work),'%H:%i:%s') AS 'Work Started', 
    IF(max(wt.finish_work) IS NULL, 'Packing', time_format(max(wt.finish_work),'%H:%i:%s')) 
    as 'Work Finished',
    (IF(timediff(pt.end_pause,pt.start_pause) > '00:00:00', (time_format(SEC_TO_TIME( SUM(distinct TIME_TO_SEC(timediff(pt.end_pause,pt.start_pause)))), '%H:%i:%s')),
        IF (time(pt.end_pause) is NULL, timediff(current_timestamp,pt.start_pause), '') ))  AS 'Pause Total',
    
    IF ('Pause Total' IS NOT NULL, time_format(timediff(IF(max(wt.finish_work) IS NULL, 'Packing',max(wt.finish_work)), min(wt.start_work)),'%H:%i:%s'),
    timediff(timediff(IF(max(wt.finish_work) IS NULL, 'Packing',max(wt.finish_work)), min(wt.start_work)),(IF(timediff(pt.end_pause,pt.start_pause) > '00:00:00', (time_format(SEC_TO_TIME( SUM(distinct TIME_TO_SEC(timediff(pt.end_pause,pt.start_pause)))), '%H:%i:%s')),
        IF (time(pt.end_pause) is NULL, timediff(current_timestamp,pt.start_pause), '') )))) as 'Worked Time'
    
    from packing_record pr 
    left join user_logins ul on pr.work_number = ul.work_number 
    left join pause_time pt on pr.work_number = pt.work_number and date_format(pr.start_pack,'%d-%m-%Y') = date_format(pt.start_pause,'%d-%m-%Y')
    right join work_time wt on pr.work_number = wt.work_number and date_format(pr.start_pack,'%d-%m-%Y') = date_format(wt.start_work,'%d-%m-%Y')
    where  date_format(wt.start_work,'%d-%m-%Y') = date_format(pr.start_pack,'%d-%m-%Y')
    #and date_format(pr.start_pack,'%Y-%m-%d') between '".$fdate."' and '".$edate."'
    group by pr.work_number, Date, date_format(wt.start_work,'%d-%m-%Y')
    order by  Date desc, Name;