SQL中针对指定ID依据另一列条件计算访问时长的实现问题

报错原因 #

你原有写法的两个子查询没有和外层查询的ID做关联，会返回所有ID的Sign In、Session End - Sign Out记录的时间值，多个返回结果无法作为表达式传入DATEDIFF函数，所以触发报错。此外你原语句中还有笔误，Foom需要修正为FROM。

正确查询方案 #

方案1：分组聚合写法（兼容性最好） #

按ID分组后通过CASE语句匹配对应行为的时间，再计算差值：

SELECT 
    ID,
    DATEDIFF(second, 
        MAX(CASE WHEN Record = 'Sign In' THEN AccessedDTTM END),
        MAX(CASE WHEN Record = 'Session End - Sign Out' THEN AccessedDTTM END)
    ) AS Duration_Seconds
FROM Audit
WHERE ID IN (16, 18)
GROUP BY ID

方案2：自连接写法（逻辑更直观） #

将同ID的登录、登出记录关联为一行后计算差值：

SELECT 
    a.ID,
    DATEDIFF(second, a.AccessedDTTM, b.AccessedDTTM) AS Duration_Seconds
FROM Audit a
INNER JOIN Audit b 
    ON a.ID = b.ID 
    AND a.Record = 'Sign In' 
    AND b.Record = 'Session End - Sign Out'
WHERE a.ID IN (16, 18)

两种方案返回的结果一致，以你的样例数据为例，ID16的时长为60秒，ID18的时长为987秒。

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