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

Question

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

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

<表类="s-表"><头>身份证个人ID类型ID日期时间<正文>1001在2022-09-01T13:21:122001结束2022-09-01T13:25:123001在2022-09-01T14:21:124001结束2022-09-01T14:25:125002在2022-09-03T13:21:126002结束2022-09-03T13:25:127002在2022-09-03T14:21:128002在2022-09-03T14:25:129002结束2022-09-03T14:25:1210002结束2022-09-03T16:25:1211002结束2022-09-03T17:25:1212002在2022-09-04T16:25:1213002在2022-09-05T17:25:12

我想创建一个 View ，返回首先按 PersonID 排序然后按 ID 排序的记录，但将行转换为列。

像这样:

<表类="s-表"><头>个人IDInIDIn_DateTime输出IDOut_DateTime<正文>00112022-09-01T13:21:1222022-09-01T13:25:1200132022-09-01T14:21:1242022-09-01T14:25:1200252022-09-03T13:21:1262022-09-03T13:25:1200272022-09-03T14:21:12空空00282022-09-03T14:25:1292022-09-03T14:25:12002空空102022-09-03T16:25:12002空空112022-09-03T17:25:12002122022-09-04T16:25:12空空002132022-09-05T17:25:12空空

有人知道如何在 MySQL 中执行此操作吗？

感谢您的任何建议。

Answer 1

使用窗口函数 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);

参见 demo .