如何计算员工每日办公总时长并动态展示多组TimeIn/TimeOut记录？

没问题，这个需求完全可以实现！由于不同员工每日的进出打卡次数不固定，静态SQL没法适配可变数量的TimeIn/TimeOut字段，我们得用动态SQL来动态生成对应数量的时段列，同时算出当日累计办公时长。

步骤1：先搞定每日累计时长（基础查询） #

如果只是需要员工每日的总办公时长，我们可以先按员工ID和日期分组，用秒数计算总时长再转成易读的时分秒格式：

SELECT 
    ID,
    CONVERT(VARCHAR(10), TimeIn, 23) AS Day,
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, TimeIn, TimeOut)), 0), 108) AS TimeSpent
FROM Attendance
GROUP BY ID, CONVERT(VARCHAR(10), TimeIn, 23)
ORDER BY Day, ID;

这个查询会得到每个员工每日的总时长，结果如下：

ID  Day         TimeSpent
1   2018-01-18  08:37:00
2   2018-01-18  08:08:00
1   2018-01-19  03:28:00
2   2018-01-19  04:26:00

步骤2：动态生成多组TimeIn/TimeOut列 #

要动态展示每个员工当日的所有进出时段，我们需要先给每个员工的每日打卡记录标记序号，再通过动态SQL生成对应数量的TimeIn和TimeOut列：

第一步：给每日打卡记录加序号

WITH RankedAttendance AS (
    SELECT 
        ID,
        TimeIn,
        TimeOut,
        CONVERT(VARCHAR(10), TimeIn, 23) AS Day,
        ROW_NUMBER() OVER (PARTITION BY ID, CONVERT(VARCHAR(10), TimeIn, 23) ORDER BY TimeIn) AS RowNum
    FROM Attendance
)
SELECT * FROM RankedAttendance;

这个CTE会给每个员工每日的打卡记录按时间顺序编号，比如ID1在2018-01-18的3次打卡会被标记为RowNum=1、2、3。

第二步：编写动态SQL生成可变列

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成动态列名：TimeIn1, TimeOut1, TimeIn2, TimeOut2...
SELECT @cols = STRING_AGG(
    CONCAT('MAX(CASE WHEN RowNum = ', RowNum, ' THEN TimeIn END) AS TimeIn', RowNum, ',',
           'MAX(CASE WHEN RowNum = ', RowNum, ' THEN TimeOut END) AS TimeOut', RowNum),
    ','
)
FROM (
    SELECT DISTINCT RowNum
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY ID, CONVERT(VARCHAR(10), TimeIn, 23) ORDER BY TimeIn) AS RowNum
        FROM Attendance
    ) t
) t;

-- 拼接完整的动态SQL
SET @sql = CONCAT(
'WITH RankedAttendance AS (
    SELECT 
        ID,
        TimeIn,
        TimeOut,
        CONVERT(VARCHAR(10), TimeIn, 23) AS Day,
        ROW_NUMBER() OVER (PARTITION BY ID, CONVERT(VARCHAR(10), TimeIn, 23) ORDER BY TimeIn) AS RowNum
    FROM Attendance
)
SELECT 
    ID,
    ', @cols, ',
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, TimeIn, TimeOut)), 0), 108) AS TimeSpent,
    Day
FROM RankedAttendance
GROUP BY ID, Day
ORDER BY Day, ID;'
);

-- 执行动态SQL
EXEC sp_executesql @sql;

执行结果说明 #

运行这段动态SQL后，会根据所有员工的最大每日打卡次数生成对应数量的TimeIn/TimeOut列。针对你的数据，会生成TimeIn1, TimeOut1, TimeIn2, TimeOut2, TimeIn3, TimeOut3三对列，结果如下：

这样就完美实现了你的需求：动态展示所有打卡时段，同时计算当日累计时长。

内容的提问来源于stack exchange，提问作者Doonie Darkoo