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;

Вы должны удалять не опубликованный вами рабочий / принятый ответ, а тот, который не является ответом.   —  person nahakubuilder    schedule 24.08.2020

См. также:  Таблица MySQL с отметками времени UNIX - сортировка по отметке времени и вычисление разницы между одной строкой и предыдущей

Вы меня очень смущаете.   —  person nahakubuilder    schedule 24.08.2020

Вы опубликовали рабочее решение и приняли его. Это хорошо. Что не хорошо, так это промежуточные сообщения, представляющие собой комментарии / частичные ответы. Поэтому отмените удаление и повторно примите опубликованный вами ответ, а второй удалите.   —  person nahakubuilder    schedule 24.08.2020

Понравилась статья? Поделиться с друзьями:
IT Шеф
Комментарии: 1
  1. nahakubuilder

    так что это решило мои проблемы:

    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;
    
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: