I have a table like this:
| ID | dtCheck | type | placeId | userId |
|---|---|---|---|---|
| 1 | 2021-12-13 13:00:00 | in | 1 | 1 |
| 2 | 2021-12-13 13:10:00 | in | 2 | 1 |
| 3 | 2021-12-13 14:00:00 | out | 1 | 1 |
| 4 | 2021-12-13 15:00:00 | in | 1 | 1 |
| 5 | 2021-12-13 16:00:00 | out | 1 | 1 |
| 6 | 2021-12-13 17:00:00 | out | 2 | 1 |
| 7 | 2021-12-13 17:00:00 | in | 2 | 1 |
| 8 | 2021-12-13 18:00:00 | out | 2 | 1 |
And I don't know how to write a select query to produce a result like this:
| dtCheckIn | dtCheckOut | placeId | userId |
|---|---|---|---|
| 2021-12-13 13:00:00 | 2021-12-13 14:00:00 | 1 | 1 |
| 2021-12-13 13:10:00 | 2021-12-13 17:00:00 | 2 | 1 |
| 2021-12-13 15:10:00 | 2021-12-13 16:00:00 | 1 | 1 |
| 2021-12-13 17:10:00 | 2021-12-13 18:00:00 | 2 | 1 |
So basically, the result will display the pair of 2 rows check in and check out into one row.
CodePudding user response：
An option to use LEAD
SELECT check_in, CASE WHEN type_out = 'out' THEN check_out ELSE NULL END check_out, place_id, user_id
FROM (
SELECT dt_check check_in, `type` type_in, LEAD(dt_check) OVER w check_out, LEAD(`type`) OVER w type_out, place_id, user_id
FROM in_out
WINDOW w AS (PARTITION BY place_id, user_id ORDER BY dt_check ASC)
) u
WHERE type_in = 'in'
ORDER BY check_in
CodePudding user response：
SELECT t1.dtCheck dtCheckin,
t2.dtCheck dtCheckOut,
placeId,
userId
FROM table t1
JOIN table t2 USING (placeId, userId)
WHERE t1.type = 'in'
AND t2.type = 'out'
AND t1.dtCheck < t2.dtCheck
AND NOT EXISTS ( SELECT NULL
FROM table t3
WHERE t1.dtCheck < t3.dtCheck
AND t3.dtCheck < t2.dtCheck
AND (t1.placeId, t1.userId) = (t3.placeId, t3.userId) )