咨询将单条登录登出记录拆分为独立行的更优SQL实现方案

问题背景 #

现有存储员工登录登出信息的表结构及数据如下，需要将每条登录-登出记录拆分为每行对应一次登录或登出的格式，同时为每个员工的打卡动作按顺序生成punch编号（登录为奇数、登出为偶数，按时间顺序递增）。

DECLARE @t TABLE
(
    id INT IDENTITY(1, 1), 
    empid VARCHAR(10), 
    logindate DATE, 
    logintime TIME, 
    logoutdate DATE, 
    logouttime TIME
)

INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES('251803', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T08:00:00.000' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T11:00:00.000' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES ('251803', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T12:00:00.000' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T16:59:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES ('251809', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T08:00:00.000' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T10:14:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES ('251809', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T11:13:00.000' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T17:20:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES ('251800', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T08:00:00.000' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T13:08:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES('251800', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T14:08:00.003' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T17:00:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES ('251800', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T18:00:00.003' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T18:00:00.003' AS TIME))
INSERT @t (empid, logindate, logintime, logoutdate, logouttime) VALUES('251800', CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T20:08:00.003' AS TIME), CAST(N'2024-06-28T00:00:00.000' AS DATETIME), CAST(N'1899-12-30T21:00:00.003' AS TIME))

现有WHILE循环实现 #

已编写WHILE循环脚本完成拆分，但希望找到更高效的替代方案：

DECLARE @clock TABLE 
(
    i INT IDENTITY,  
    empid VARCHAR(10), 
    datetimelog DATETIME, 
    log_status VARCHAR(10), 
    punch VARCHAR(10)
)

DECLARE @i INT = 1, @pnum INT, @curempid VARCHAR(10)

DECLARE @empid VARCHAR(10), 
        @logindate DATE, @logintime TIME, 
        @logoutdate DATE, @logouttime TIME

WHILE (@i <= (SELECT MAX(id)FROM @t))
BEGIN
    SELECT 
        @empid = empid, 
        @logindate = logindate, @logintime = logintime, 
        @logoutdate = logoutdate, @logouttime = logouttime
    FROM 
        @t 
    WHERE 
        id = @i 
    ORDER BY 
        empid, logindate

    
    IF (@curempid IS NULL)
    BEGIN
        SET @curempid = @empid
        SET @pnum = 1
    END
    ELSE
    BEGIN
        IF (@curempid = @empid)
        BEGIN
            SET @pnum = @pnum + 1
        END
        ELSE
        BEGIN
            SET @curempid = @empid
            SET @pnum = 1
        END
    END

    INSERT INTO @clock (empid, datetimelog, log_status, punch)
    VALUES (@empid, CAST(@logindate AS DATETIME) + CAST(CAST(@logintime AS TIME) AS DATETIME), 'IN', @pnum)
    
    SET @pnum = @pnum + 1
    
    INSERT INTO @clock (empid, datetimelog, log_status, punch)
    VALUES (@empid, CAST(@logoutdate AS DATETIME) + CAST(CAST(@logouttime AS TIME) AS DATETIME), 'OUT', @pnum)

    SET @i = @i + 1;
END;

更优实现：基于集合的拆分（无需循环） #

WHILE循环属于逐行处理，效率低于集合式操作。可以用CROSS APPLY结合VALUES子句直接将每行拆分为登录、登出两行，再通过窗口函数生成punch编号，完全替代循环：

DECLARE @clock TABLE 
(
    i INT IDENTITY,  
    empid VARCHAR(10), 
    datetimelog DATETIME, 
    log_status VARCHAR(10), 
    punch INT
)

INSERT INTO @clock (empid, datetimelog, log_status, punch)
SELECT 
    empid,
    datetimelog,
    log_status,
    ROW_NUMBER() OVER (PARTITION BY empid ORDER BY datetimelog) AS punch
FROM @t
CROSS APPLY (
    VALUES 
        -- 登录记录
        (CAST(logindate AS DATETIME) + CAST(logintime AS DATETIME), 'IN'),
        -- 登出记录
        (CAST(logoutdate AS DATETIME) + CAST(logouttime AS DATETIME), 'OUT')
) AS ca(datetimelog, log_status)
ORDER BY empid, datetimelog;

-- 查看结果
SELECT * FROM @clock;

方案优势 #

• 性能更优：集合式操作是SQL Server优化器最擅长的处理方式，避免了循环的逐行开销，数据量越大优势越明显。
• 代码更简洁：去掉了大量变量声明和循环逻辑，可读性更高，维护成本低。
• 逻辑更可靠：窗口函数ROW_NUMBER()按员工分组、按打卡时间排序生成punch编号，完全匹配原循环的编号逻辑，且避免了循环中可能出现的变量状态错误。

关于Pivot的说明 #

你提到的Pivot是将行转成列的操作，而当前需求是将每行拆分为多行（列转行），对应应该用Unpivot或者上述的CROSS APPLY + VALUES方式，Pivot并不适用于这个场景。

内容的提问来源于stack exchange，提问作者Dante Salvador