mysql - 根据mysql中的某些条件将一条记录拆分为两条记录

Question

我负责清理数据，其中包括正常轮类和夜类数据。表中有站点进入时间、站点退出时间、房间进入时间、房间退出时间等字段，用于计算存在时间和工作时间等关键绩效指标。在正常轮类的情况下，我会在 mysl 表中提供的单个记录中显示员工旅程。

站点名称、员工、站点进入时间、任务、站点退出时间

如果是夜类，我想将一条记录分成两条记录。例如- 一名员工于 06-02-2019 21:00:00 进入站点，他于 07-02-2019 04:00 离开站点:00。在第一条记录中，我想显示网站进入时间 - 06-02-2019 21:00:00 ，网站退出时间 - 06-02-2019 11:59:59在第二条记录中，站点进入时间将为 - 07-02-2019 12:00:00，站点退出时间将为 - 07-02-2019 04:00:00

    create table smartview.test_table1(
Employee varchar(45),
site_name varchar(45),
Employee_code varchar(45),
Room_no varchar(45),
Task_type varchar(45),
Site_Enter_Time timestamp,
Room_Enter_Time timestamp,
Room_Exit_Time timestamp,
Site_Exit_Time timestamp
);


insert into smartview.test_table1(Employee,site_name,Employee_code,Room_no,Task_type,Site_Enter_Time,Room_Enter_Time,Room_Exit_Time,Site_Exit_Time) values
('Sharma','test_site1','003','Room2','floor cleaning',STR_TO_DATE('02-03-2019 14:00:00','%m-%d-%Y %H:%i:%s'),STR_TO_DATE('02-03-2019 16:00:00','%m-%d-%Y %H:%i:%s'),STR_TO_DATE('02-03-2019 23:00:00','%m-%d-%Y %H:%i:%s'),STR_TO_DATE('02-04-2019 02:00:00','%m-%d-%Y %H:%i:%s'));

Answer 1

您可以使用UNION 创建两行，其中进入和退出时间分布在不同的日期。简化查询:

-- shift ends on same day
SELECT employee, site_enter_time, site_exit_time
FROM test_table1
WHERE DATE(site_enter_time) = DATE(site_exit_time)

UNION ALL
-- shift ends on next day: first record
SELECT employee, site_enter_time, DATE(site_exit_time) - INTERVAL 1 SECOND
FROM test_table1
WHERE DATE(site_enter_time) = DATE(site_exit_time) - INTERVAL 1 DAY

UNION ALL
-- shift ends on next day: second record
SELECT employee, DATE(site_exit_time), site_exit_time
FROM test_table1
WHERE DATE(site_enter_time) = DATE(site_exit_time) - INTERVAL 1 DAY