我寫了一個MySQL 查詢，它從clockInTest表中找到缺少的出勤率，然后在當天的結束時間插入一個新行。但是，我還需要計算那幾天（新插入的）的總辦公時間和休息時間，并且我必須使用 timeSpan 和 breakTime更新WorkDay表。以下是我正在使用的示例資料：
在執行我的查詢之前：
| ID | 時間戳 | 作業日編號 | 員工ID | 型別 |
|---|---|---|---|---|
| 1 | 2021-10-26 08:00:00 | 149 | 1 | 開始 |
| 2 | 2021-10-25 08:00:00 | 148 | 1 | 開始 |
| 3 | 2021-10-26 10:00:00 | 149 | 1 | 結尾 |
| 4 | 2021-10-26 12:00:00 | 149 | 1 | 開始 |
執行以下查詢后：
START TRANSACTION;
INSERT INTO ClockInTest (PartnerId, Timestamp, WorkDayId, UserId, EmployeeId, Type )
SELECT a.PartnerId, CONCAT(DATE(a.TimeStamp),' 23:59:00'), a.WorkDayId, a.UserId, a.EmployeeId, 'End'
FROM ClockInTest a
WHERE a.Type = 'Start'
AND a.DeletedAt IS NULL
AND a.TIMESTAMP <= NOW() - INTERVAL 1 DAY
AND NOT EXISTS (
SELECT 1
FROM ClockInTest b
WHERE b.EmployeeId = a.EmployeeId
AND a.WorkDayId = b.WorkDayId
AND a.Timestamp < b.Timestamp
AND b.Type = 'End'
AND b.DeletedAt IS NULL
) GROUP BY a.WorkDayId, a.EmployeeId;
SELECT * FROM clockintest WHERE WorkDayId IN (
SELECT DISTINCT c.WorkDayId FROM clockintest c WHERE c.Id >= LAST_INSERT_ID());
COMMIT;
查詢執行后的clockInTest表：
| ID | 時間戳 | 作業日編號 | 員工ID | 型別 |
|---|---|---|---|---|
| 1 | 2021-10-26 08:00:00 | 149 | 1 | 開始 |
| 2 | 2021-10-25 08:00:00 | 148 | 1 | 開始 |
| 3 | 2021-10-26 10:00:00 | 149 | 1 | 結尾 |
| 4 | 2021-10-26 12:00:00 | 149 | 1 | 開始 |
| 5 | 2021-10-26 23:59:00 | 149 | 1 | 結尾 |
| 6 | 2021-10-25 23:59:00 | 148 | 1 | 結尾 |
So, the above table missing data are added. Now, I have to calculate the total working hours (timeSpan) and break time for each WorkDayId and after that, I have to update the WorkDay table which is like following:
WorkDay table
| Id | TimeSpan | EmployeeId | BreakTime |
|---|---|---|---|
| 148 | 1 | ||
| 149 | 1 |
Expected Result in WorkDay Table:
| Id | TimeSpan | EmployeeId | BreakTime |
|---|---|---|---|
| 148 | 57540000 | 1 | 0 |
| 149 | 50,328,000 | 1 | 7200000 |
** TimeSpan and BreakTime in miliseconds
uj5u.com熱心網友回復：
我想我們可以結合每種Start型別的表資料，并找到各自的End時間和Next Start時間（如果有的話）
WorkDayId | EmployeeId | Start | End | Next Start
148 | 1 | 2021-10-25 08:00:00 | 2021-10-25 23:59:00
149 | 1 | 2021-10-26 08:00:00 | 2021-10-26 10:00:00 | 2021-10-26 12:00:00
149 | 1 | 2021-10-26 12:00:00 | 2021-10-26 23:59:00 |
那么預期的結果只是時間差異的總和
SELECT
a.WorkDayId,
a.EmployeeId,
SUM(TIMESTAMPDIFF(SECOND, a.`TimeStamp`, b.`TimeStamp`)) * 1000 AS timespan,
SUM(CASE WHEN c.`Timestamp` IS NULL THEN 0 ELSE TIMESTAMPDIFF(SECOND, b.`TimeStamp`, c.`TimeStamp`) END) * 1000 AS breakTime
FROM clockInTest a
JOIN clockInTest b
ON a.EmployeeId = b.EmployeeId
AND a.WorkDayId = b.WorkDayId
AND b.`Timestamp` > a.`Timestamp`
LEFT JOIN clockInTest c
ON a.EmployeeId = c.EmployeeId
AND a.WorkDayId = c.WorkDayId
AND c.`Type` = 'Start'
AND c.`Timestamp` > b.`Timestamp`
WHERE a.`Type` = 'Start'
AND b.`Type` = 'End'
AND NOT EXISTS (
SELECT 1
FROM clockInTest d
WHERE a.EmployeeId = d.EmployeeId
AND a.WorkDayId = d.WorkDayId
AND (
( d.`Timestamp` > a.`Timestamp` AND d.`Timestamp` < b.`Timestamp` )
OR ( d.`Timestamp` > b.`Timestamp` AND d.`Timestamp` < c.`Timestamp` )
)
)
GROUP BY a.WorkDayId, a.EmployeeId
ORDER BY a.WorkDayId, a.EmployeeId
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=eb3b1fef815f1201554fdca683a8448f
要將結果更新到WorkDay表中，假設 WorkDayId 和 EmployeeId 記錄存在且 timespan 和 breakTime 為空
UPDATE WorkDay w
JOIN (
... // the above select query
) g ON w.id = g.WorkDayId AND w.EmployeeId = g.EmployeeId
SET w.TimeSpan = g.timespan, w.BreakTime = g.breakTime
;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2262e5156c0a991eddbbb39ebacfd3bf
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/341360.html
標籤：mysql sql database mariadb database-administration