如何编写MySQL查询实现行转列（员工考勤场景）

我正在开发一款员工追踪应用，现有如下结构的MySQL数据表：

我希望创建一个视图，返回的记录先按PersonID排序，再按ID排序，同时将行转换为列，格式如下：

请问如何在MySQL中实现这个需求？

要实现这个需求，核心是为每个PersonID下的IN和OUT记录分别分配分组内序号，再通过序号将对应的记录配对，同时保留无法匹配的单独记录。以下是具体实现步骤：

1. 创建视图的完整SQL语句 #

CREATE VIEW employee_tracking_view AS
WITH in_records AS (
    -- 提取所有IN记录，为每个员工的IN操作按ID顺序分配序号
    SELECT 
        PersonID,
        ID AS InID,
        DateTime AS In_DateTime,
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY ID) AS in_seq
    FROM your_table_name
    WHERE TypeID = 'IN'
),
out_records AS (
    -- 提取所有OUT记录，为每个员工的OUT操作按ID顺序分配序号
    SELECT 
        PersonID,
        ID AS OutID,
        DateTime AS Out_DateTime,
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY ID) AS out_seq
    FROM your_table_name
    WHERE TypeID = 'OUT'
)
-- 全连接匹配IN/OUT记录，保留所有单独的IN或OUT行
SELECT 
    COALESCE(i.PersonID, o.PersonID) AS PersonID,
    i.InID,
    i.In_DateTime,
    o.OutID,
    o.Out_DateTime
FROM in_records i
FULL JOIN out_records o 
    ON i.PersonID = o.PersonID 
    AND i.in_seq = o.out_seq
-- 按PersonID和序号排序，保证顺序符合要求
ORDER BY PersonID, COALESCE(i.in_seq, o.out_seq);

2. 逻辑说明 #

• 分组序号分配：使用ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY ID)，为每个员工的IN、OUT操作分别按原始ID顺序生成递增序号，确保后续能按顺序配对。
• 全连接匹配：FULL JOIN会保留所有IN和OUT记录，即使没有对应的配对项（比如单独的IN或OUT），未匹配的字段会显示为NULL。
• 排序规则：最终结果按PersonID分组，再按分组内序号排序，和需求中的顺序一致。

3. 注意事项 #

• 该方案要求MySQL版本为8.0及以上，因为使用了窗口函数ROW_NUMBER()和CTE（WITH子句）。
• 请将SQL中的your_table_name替换为你实际的数据表名称。

内容的提问来源于stack exchange，提问作者davor.geci