Блог

Главная - Вопросы по программированию - MySQL Объединяет 3 таблицы в одном общем столбце и изменяет столбцы

MySQL Объединяет 3 таблицы в одном общем столбце и изменяет столбцы

#mysql #mariadb

#mysql #mariadb

Вопрос:

Я использую 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 и date, плюс он создает пользовательские столбцы, где указано общее время из work_time за весь день и общее время паузы из pause_time таблицы.
Из первой таблицы только СУММА ( 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;

Комментарии:

1. Вы не должны удалять рабочий / принятый ответ, который вы опубликовали, но другой, который не является ответом.

2. Вы, ребята, меня очень смущаете.

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

Ответ №1:

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

 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;