如何创建将行转换为列的MySQL查询

Question

我正在创建一个员工跟踪应用程序。

我有一个MySQL表，如下所示：

输出[/tr>输出[/tr>输出[/tr>ININ[/tr>

	ID
1	001	IN	2022-09-01T13:2:12
2	001	2022-09-01T13:25:12
3	001	IN	2022-09-01T14:2:12
4	001	输出	2022-09-01T14:25:12
5	002	IN	2022-09-03T13:2:12
6	002	输出	2022-09-03T13:25:12
7	002	IN	2022-09-03T14:2:12
8	002	IN	2022-09-03T14:25:12
9	002	输出	2022-09-03T14:25:12
10	002	2022-09-03T16:25:12
11	002	2022-09-03T17:25:12
12	002	2022-09-04T16:25:12
13	002	2022-09-05T17:25:12

使用窗口函数LEAD()或LAG()根据其TypeID为每一行获取其配对行，并将结果左连接到表：

WITH cte AS (
SELECT *, 
CASE 
WHEN TypeID = 'IN' AND LEAD(TypeID) OVER w = 'OUT' THEN LEAD(ID) OVER w
WHEN TypeID = 'OUT' AND LAG(TypeID) OVER w = 'IN' THEN LAG(ID) OVER w
END other_ID  
FROM tablename
WINDOW w AS (PARTITION BY PersonID ORDER BY DateTime)
)
SELECT DISTINCT c.PersonID,
CASE WHEN c.TypeID = 'IN' THEN c.ID ELSE t.ID END InID,
CASE WHEN c.TypeID = 'IN' THEN c.DateTime ELSE t.DateTime END In_DateTime,
CASE WHEN c.TypeID = 'IN' THEN t.ID ELSE c.ID END OutID,
CASE WHEN c.TypeID = 'IN' THEN t.DateTime ELSE c.DateTime END Out_DateTime
FROM cte c LEFT JOIN tablename t
ON t.ID = c.other_ID
ORDER BY c.PersonID, COALESCE(In_DateTime, Out_DateTime);

请参阅演示