Я использую базу данных 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
Вы меня очень смущаете. — person nahakubuilder schedule 24.08.2020
Вы опубликовали рабочее решение и приняли его. Это хорошо. Что не хорошо, так это промежуточные сообщения, представляющие собой комментарии / частичные ответы. Поэтому отмените удаление и повторно примите опубликованный вами ответ, а второй удалите. — person nahakubuilder schedule 24.08.2020
так что это решило мои проблемы: