如何编写MySQL查询获取员工在各地点的停留时长报表

需求背景 #

员工进入或离开房间时，系统会自动向数据库表插入记录。基于现有数据表，需要生成包含员工ID、位置、日期、进入时间、离开时间及停留时长的统计报表。

现有数据表结构与数据 #

id empid location   CreatedDate
1   1   Block 1     2023-06-01 08:31:16
2   2   Block 1     2023-06-01 08:31:16
3   3   Block 1     2023-06-01 08:31:16
4   4   Block 2     2023-06-01 08:31:16
5   1   Block 1     2023-06-01 08:51:25
6   1   Block 1     2023-06-01 09:15:02
7   2   Block 1     2023-06-01 09:15:15
8   2   Block 2     2023-06-01 09:20:12
9   1   Block 1     2023-06-01 09:31:22
10   1   Block 2     2023-06-01 09:35:22
11   3   Block 1     2023-06-01 09:37:05
12   1   Block 2     2023-06-01 09:40:08

期望输出的统计报表格式 #

Empid     location    Date        arrived     left        total_spend(seconds)
       1        Block1      2023-06-01  08:31:16    08:51:25    1209
       1        Block1      2023-06-01  09:15:02    09:31:22    980
       1        Block2      2023-06-01  09:35:22    09:40:08    284
       2        Block1      2023-06-01  08:31:16    09:15:15    2639
       2        Block2      2023-06-01  09:20:12    -            -

注：原示例报表中Block2的arrived时间06:35:22应为笔误，实际对应数据为09:35:22。

实现的MySQL查询语句 #

WITH employee_records AS (
    SELECT 
        empid,
        location,
        CreatedDate,
        -- 按员工+位置分组，对记录按时间排序生成序列
        ROW_NUMBER() OVER (PARTITION BY empid, location ORDER BY CreatedDate) AS seq
    FROM your_table_name -- 替换为实际数据表名
)
SELECT 
    er_in.empid,
    REPLACE(er_in.location, ' ', '') AS location,
    DATE(er_in.CreatedDate) AS `Date`,
    TIME(er_in.CreatedDate) AS arrived,
    CASE 
        WHEN er_out.CreatedDate IS NOT NULL THEN TIME(er_out.CreatedDate) 
        ELSE '-' 
    END AS `left`,
    CASE 
        WHEN er_out.CreatedDate IS NOT NULL THEN TIMESTAMPDIFF(SECOND, er_in.CreatedDate, er_out.CreatedDate) 
        ELSE '-' 
    END AS `total_spend(seconds)`
FROM employee_records er_in
LEFT JOIN employee_records er_out 
    ON er_in.empid = er_out.empid 
    AND er_in.location = er_out.location 
    AND er_in.seq + 1 = er_out.seq
WHERE er_in.seq % 2 = 1 -- 取奇数序列作为进入记录
ORDER BY er_in.empid, er_in.location, er_in.CreatedDate;

语句逻辑说明 #

1. CTE分组排序：通过employee_records公共表表达式，对每个员工在同一位置的记录按时间排序，生成序列编号seq，用于区分进出记录。
2. 关联进出记录：将进入记录（奇数seq）与对应的离开记录（偶数seq，即seq+1）左连接，保证未离开的记录也能被查询到。
3. 格式处理：去掉location中的空格以匹配报表格式，提取日期和时间部分；无离开记录时，left和total_spend(seconds)字段显示-。
4. 结果排序：按员工ID、位置、进入时间排序，与期望报表顺序一致。

内容的提问来源于stack exchange，提问作者Durai Raj