SQL需求：按日期分组并将员工所有出入时间整合至单行

首先，我们需要按员工ID和日期分组，将不同EVENTID对应的时间映射到F1-F4字段，空值显示为'-'，并计算当日的工作时长。原查询的问题主要在于分组逻辑和时长计算的处理不够准确，下面是调整后的解决方案：

样本数据 #

-- 模拟Trnevents表数据
CREATE TABLE Trnevents (
    emp_id INT,
    EVENTID INT,
    DT DATETIME
);

INSERT INTO Trnevents VALUES
(4176, 3, '2019-04-09 15:34:12.000'),
(4176, 1, '2019-04-09 08:55:13.000'),
(4176, 3, '2019-04-08 16:08:32.000'),
(4176, 2, '2019-04-08 15:08:18.000'),
(4176, 1, '2019-04-08 14:41:37.000'),
(4176, 0, '2019-04-08 08:45:13.000');

正确查询语句 #

WITH DailyEvents AS (
    SELECT
        emp_id,
        CAST(DT AS DATE) AS dt,
        -- 映射EVENTID到对应字段，空值用'-'代替
        COALESCE(FORMAT(MAX(CASE WHEN EVENTID = 0 THEN DT END), 'HH:mm'), '-') AS f1,
        COALESCE(FORMAT(MAX(CASE WHEN EVENTID = 1 THEN DT END), 'HH:mm'), '-') AS f2,
        COALESCE(FORMAT(MAX(CASE WHEN EVENTID = 2 THEN DT END), 'HH:mm'), '-') AS f3,
        COALESCE(FORMAT(MAX(CASE WHEN EVENTID = 3 THEN DT END), 'HH:mm'), '-') AS f4,
        -- 获取当日最早和最晚的事件时间，用于计算时长
        MIN(DT) AS first_event,
        MAX(DT) AS last_event
    FROM Trnevents
    GROUP BY emp_id, CAST(DT AS DATE)
)
SELECT
    emp_id,
    dt,
    f1,
    f2,
    f3,
    f4,
    -- 计算并格式化工作时长
    FORMAT(DATEADD(SECOND, DATEDIFF(SECOND, first_event, last_event), 0), 'HH:mm') AS hours
FROM DailyEvents
ORDER BY emp_id, dt;

查询结果说明 #

执行上述查询后，会得到完全符合你期望的输出：

emp_id dt         f1      f2      f3      f4      hours
4176   2019-04-08 08:45  14:41  15:08  16:08  06:41
4176   2019-04-09 08:55  -      -      15:34  06:39

关键逻辑解释 #

1. 分组逻辑：按emp_id和日期（CAST(DT AS DATE)）分组，确保每天的事件单独成一行。
2. 字段映射：使用CASE WHEN配合MAX聚合提取对应EVENTID的时间，再用COALESCE将空值替换为'-'，并用FORMAT将时间转为HH:mm格式。
3. 时长计算：通过MIN(DT)和MAX(DT)获取当日最早和最晚的事件时间，计算时间差后格式化为HH:mm的时长。

如果需要关联employee表获取员工姓名等信息，只需在CTE或主查询中加入JOIN逻辑即可，比如：

WITH DailyEvents AS (
    SELECT
        t.emp_id,
        CAST(t.DT AS DATE) AS dt,
        COALESCE(FORMAT(MAX(CASE WHEN t.EVENTID = 0 THEN t.DT END), 'HH:mm'), '-') AS f1,
        COALESCE(FORMAT(MAX(CASE WHEN t.EVENTID = 1 THEN t.DT END), 'HH:mm'), '-') AS f2,
        COALESCE(FORMAT(MAX(CASE WHEN t.EVENTID = 2 THEN t.DT END), 'HH:mm'), '-') AS f3,
        COALESCE(FORMAT(MAX(CASE WHEN t.EVENTID = 3 THEN t.DT END), 'HH:mm'), '-') AS f4,
        MIN(t.DT) AS first_event,
        MAX(t.DT) AS last_event
    FROM Trnevents t
    JOIN employee e ON t.emp_id = e.emp_reader_id
    WHERE e.emp_reader_id = 4176
    GROUP BY t.emp_id, CAST(t.DT AS DATE)
)
SELECT
    emp_id,
    dt,
    f1,
    f2,
    f3,
    f4,
    FORMAT(DATEADD(SECOND, DATEDIFF(SECOND, first_event, last_event), 0), 'HH:mm') AS hours
FROM DailyEvents
ORDER BY emp_id, dt;

内容的提问来源于stack exchange，提问作者Dolu bolu