如何将IN/OUT独立行的时间数据转为两列并计算时长差

现有一张记录时间进出的表，IN和OUT时间以独立行存储，需要将IN和OUT时间合并为两列，以此计算每次进出的耗时。

表结构如下：

Time                          TimeType
2022-04-04 09:13:19.000         IN
2022-04-04 09:20:54.000         OUT
2022-04-04 09:21:54.000         IN
2022-04-04 09:25:54.000         OUT
2022-04-04 09:26:54.000         IN
2022-04-04 09:28:54.000         IN

期望查询结果：

inTime                          outTime                   timeSpent
2022-04-04 09:13:19.000         2022-04-04 09:20:54.000    7
2022-04-04 09:21:54.000         2022-04-04 09:25:54.000    4
2022-04-04 09:26:54.000         NULL                       0

注：NULL表示异常记录，后续可忽略这类值。

已尝试的方法及问题：

• 子查询写法：

SELECT (SELECT Times AS inTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='IN' AND personID='1'),
(SELECT Times AS outTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='OUT' AND personID='1')

报错，原因是子查询返回多行结果，不符合单值要求。

• JOIN写法：

SELECT A.times AS inTime, B.times AS outTime FROM Table A
INNER JOIN Table B ON A.personID=B.personID
WHERE A.Times>='2022-04-04 00:00:00' AND A.Times<'2022-04-04 23:59:59' AND A.timeType='IN' AND A.personID='1' AND B.Times>='2022-04-04 00:00:00' AND B.Times<'2022-04-04 23:59:59' AND B.timeType='IN' AND B.personID='1' 

出现inTime和outTime重复显示的问题。

可以通过窗口函数为每个IN记录匹配后续最近的OUT记录，具体SQL如下（以MySQL为例，其他数据库可调整函数语法）：

WITH in_records AS (
    SELECT 
        Time AS inTime,
        ROW_NUMBER() OVER (ORDER BY Time) AS rn
    FROM your_table_name
    WHERE TimeType = 'IN' AND personID = '1' AND Time BETWEEN '2022-04-04 00:00:00' AND '2022-04-04 23:59:59'
),
out_records AS (
    SELECT 
        Time AS outTime,
        ROW_NUMBER() OVER (ORDER BY Time) AS rn
    FROM your_table_name
    WHERE TimeType = 'OUT' AND personID = '1' AND Time BETWEEN '2022-04-04 00:00:00' AND '2022-04-04 23:59:59'
)
SELECT 
    ir.inTime,
    ors.outTime,
    CASE 
        WHEN ors.outTime IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, ir.inTime, ors.outTime)
        ELSE 0
    END AS timeSpent
FROM in_records ir
LEFT JOIN out_records ors ON ir.rn = ors.rn
ORDER BY ir.inTime;

逻辑说明： #

1. 先分别筛选出所有IN和OUT记录，用ROW_NUMBER()按时间排序生成序号rn；
2. 通过LEFT JOIN按序号匹配对应的IN和OUT记录，确保每个IN都能找到对应的OUT（无对应则为NULL）；
3. 用TIMESTAMPDIFF计算分钟级耗时，无OUT时耗时设为0。

如果是SQL Server，可将TIMESTAMPDIFF(MINUTE, ir.inTime, ors.outTime)替换为DATEDIFF(MINUTE, ir.inTime, ors.outTime)。

如果存在连续多个IN的情况（如示例中最后两条IN），该方案会将第一个IN匹配后续第一个OUT，后续无对应OUT的IN则显示NULL，符合需求。

内容的提问来源于stack exchange，提问作者Mzgks Studio