我正在创建一款员工跟踪应用程序.
我有一个MySQL表,如下所示:
ID | PersonID | TypeID | DateTime |
---|---|---|---|
1 | 001 | IN | 2022-09-01T13:21:12 |
2 | 001 | OUT | 2022-09-01T13:25:12 |
3 | 001 | IN | 2022-09-01T14:21:12 |
4 | 001 | OUT | 2022-09-01T14:25:12 |
5 | 002 | IN | 2022-09-03T13:21:12 |
6 | 002 | OUT | 2022-09-03T13:25:12 |
7 | 002 | IN | 2022-09-03T14:21:12 |
8 | 002 | IN | 2022-09-03T14:25:12 |
9 | 002 | OUT | 2022-09-03T14:25:12 |
10 | 002 | OUT | 2022-09-03T16:25:12 |
11 | 002 | OUT | 2022-09-03T17:25:12 |
12 | 002 | IN | 2022-09-04T16:25:12 |
13 | 002 | IN | 2022-09-05T17:25:12 |
我想创建一个返回记录的视图,首先按PersonID排序,然后按ID排序,但将行转换为列.
大概是这样的:
PersonID | InID | In_DateTime | OutID | Out_DateTime |
---|---|---|---|---|
001 | 1 | 2022-09-01T13:21:12 | 2 | 2022-09-01T13:25:12 |
001 | 3 | 2022-09-01T14:21:12 | 4 | 2022-09-01T14:25:12 |
002 | 5 | 2022-09-03T13:21:12 | 6 | 2022-09-03T13:25:12 |
002 | 7 | 2022-09-03T14:21:12 | null | null |
002 | 8 | 2022-09-03T14:25:12 | 9 | 2022-09-03T14:25:12 |
002 | null | null | 10 | 2022-09-03T16:25:12 |
002 | null | null | 11 | 2022-09-03T17:25:12 |
002 | 12 | 2022-09-04T16:25:12 | null | null |
002 | 13 | 2022-09-05T17:25:12 | null | null |
有人知道如何在MySQL中做到这一点吗?
谢谢你的建议.