我有一張這樣的表（pec_za_starenje_prva_start_stop）：
| ID | 時間記錄 | 標簽1 |
|---|---|---|
| 1 | 2022-10-25 21:56:19 | 1 |
| 2 | 2022-10-26 04:13:24 | 0 |
| 3 | 2022-10-26 05:42:54 | 1 |
| 4 | 2022-10-26 12:18:27 | 0 |
| 5 | 2022-10-26 14:29:36 | 1 |
| 6 | 2022-10-26 21:22:21 | 0 |
id | int
TimeLogged | DateTime
Tag1 | int
“Tag1”中的數字 1 應表示回圈已開始，0 應表示回圈已結束。所以，我需要看起來像這樣的表：
| ID | 開始回圈 | end_cycle | 總時間 |
|---|---|---|---|
| 1 | 2022-10-25 21:56:19 | 2022-10-26 04:13:24 | 377分鐘 |
| 2 | 2022-10-26 05:42:54 | 2022-10-26 12:18:27 | 395分鐘 |
| 3 | 2022-10-26 14:29:36 | 2022-10-26 21:22:21 | 1132分鐘 |
總時間采用哪種格式并不重要，這只是一個示例。
這是我發現的最接近的東西，但它不能正常作業，因為它只顯示開始時間：
SELECT ID,
TimeLogged start_cycle,
next_TimeLogged end_cycle,
TIMEDIFF(next_TimeLogged, TimeLogged) total_time
FROM (
SELECT *, LEAD(TimeLogged) OVER (PARTITION BY ID ORDER BY TimeLogged)
next_TimeLogged
FROM pec_za_starenje_prva_start_stop
) t
WHERE Tag1 = '1'
ORDER BY start_cycle;
uj5u.com熱心網友回復：
WITH
cte AS (
SELECT TimeLogged TimeLoggedIn,
LEAD(TimeLogged) OVER win TimeLoggedOut,
Tag1,
LEAD(Tag1) OVER win tag2
FROM pec_za_starenje_prva_start_stop
WINDOW win AS (ORDER BY TimeLogged)
)
SELECT ROW_NUMBER() OVER (ORDER BY TimeLoggedIn) ID,
TimeLoggedIn start_cycle,
TimeLoggedOut end_cycle,
TIMESTAMPDIFF(MINUTE, TimeLoggedIn, TimeLoggedOut) total_time
FROM cte
WHERE Tag1 AND !Tag2;
https://dbfiddle.uk/NVW9YGb0
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/521037.html
標籤：mysqlsql
上一篇：按主鍵限制有序SQL查詢